Блог

Рассматриваемая электронная таблица

Мой поставщик энергии выводит довольно запутанный CSV-файл с данными под несколькими бесполезными заголовками на одном листе. Мне нужен только один тип данных («Данные E1»).

Я хочу создать скрипт, который автоматически разделяет соответствующие данные (между строками 244-485 в этой версии). Однако эти номера строк меняются каждый день при обновлении по мере добавления новых данных в каждый раздел.

Заголовки для каждого раздела согласованы при каждом обновлении CSV, могу ли я создать скрипт для сортировки данных на основе текста в этом заголовке (в настоящее время в строке 244)?

200 6102111110 B1E1K1Q1 E1 E1 DZ152889 кВт * Ч 30

Это должен быть какой-то скрипт, определяющий значения между указанным выше заголовком («Данные E1») и заголовком «K1» (строка 486).

Не уверен, возможно ли это, но я новичок в создании скриптов. Сначала я попытался провести тщательную проверку с помощью исследований!

В противном случае я мог бы создать скрипт, который вычисляет строку, с которой будут начинаться данные E1, и использует это значение? Я придумал формулу в электронной таблице, которая показывает, как можно найти эти номера строк, но не уверен, как создать скрипт на основе этого!

Вот скрипт, который работает для этой текущей таблицы, но не будет работать в другие дни, так как строки будут меняться:

 function CopyE1Data() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheets()[0];
  var destination = ss.getSheets()[1];

  var range = source.getRange("A245:AX485")
  range.copyValuesToRange(destination, 1, 2, 2, 2);
  
    }       
                                                                                                                                    
 

A Кажется, что столбец указывает тип строки. Используя это, мы можем определить заголовки (значение 200) и выполнить поиск E1 в четвертой строке, мы можем определить этот заголовок. Вот сценарий, который я придумал (краткое изложение в конце)

 function CopyE1Data() {
 const ss = SpreadsheetApp.getActiveSpreadsheet()
 const source = ss.getSheets()[0]
 const dest = ss.getSheets()[1]
 const rows = source.getRange(1, 1, source.getLastRow()-1, 4).getValues()
 
 // Find the rows
 let i = 0
 let start
 let end
 while (i < rows.length) {
   if (rows[i][3] === 'E1') {
     start = i   2 //  1 to skip the current row (only copy data) and  1 to convert from index to row number
     break
   } else {
     i  
   }
 }
 // The current row is the E1 header. Skip it.
 i  
 while (i < rows.length) {
   // Stop at next header or at the end of the sheet
   if (rows[i][0] === 200) {
     end = i   1 //  1 to convert from index to row number
     break
   } else {
     i  
   }
 }
 
 // Copy values
 source.getRange(start, 1, end-start, 50).copyTo(dest.getRange(1, 1))
}
 

Краткое изложение

1. Мы получаем листы и необходимые данные (4 столбца; все строки)
2. Мы ищем строку с E1 в столбце 4. Сохраните следующую строку (мы не копируем заголовок).
3. Мы пропускаем эту строку и продолжаем итерацию, пока не найдем другой заголовок или не дойдем до конца.
4. Скопируйте значения на другой лист.

Возможно, вы захотите получить листы по имени вместо использования индекса, так как это позволит вам изменить порядок и добавить другие листы перед ними.

Ссылки

• Диапазон CopyTo (назначение) (разработчики Google)
• Sheet getLastRow() (разработчики Google)
• Range getValues() (разработчики Google)
• Электронная таблица getSheetByName(имя) (разработчики Google)

Вот решение с использованием метода indexOf().

setValues() задает прямоугольную сетку значений, сохраняя исходный формат.

 function extractData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const source = ss.getSheets()[0];
  const dest = ss.getSheets()[1];
  const lr = source.getLastRow();
  const arrE1 = source.getRange(1, 4, lr, 1).getValues();
  const fE1 = [].concat(...arrE1); // Make the array flat
  let rE1 = fE1.indexOf("E1")   2; // Search for the starting row's number of the range with the required data
  
  let arr200 = source.getRange(rE1, 1, lr, 1).getValues();
  let f200 = [].concat(...arr200); // Make the array flat
  let r200 = f200.indexOf(200); // Search for the ending row's number of the range with the required data
  
  let data = source.getRange(rE1, 1, r200, 50).getValues();
  dest.getRange(2, 1, data.length, 50).setValues(data);
}