Блог

Этот вопрос похож на «Обработка данных форм в Google Sheets» (https://webapps.stackexchange.com/questions/88736/forms-data-manipulation-in-google-sheets ), но требует немного большей автоматизации:

Справочная информация: Пользователи заполняют форму Google для запроса и имеют возможность повторить те же вопросы для заполнения второго, третьего, четвертого и пятого запросов. Я создал таблицу, которая будет манипулировать этими строками таким образом, чтобы строки с одинаковыми столбцами были перенесены в один столбец.

Вот мой пример таблицы: https://docs.google.com/spreadsheets/d/11DM7z_vwuR1S6lgMN7Wu7a0GoouVc2_5xj6nZ1Ozj5I/edit#gid=1967901028

Ответы формы: таблица, которая возвращает ответы от пользователей, заполняющих форму

Обработанные строки: таблица, которая возвращает обработанные строки, используя: =OFFSET(‘Ответы формы’!$ A $ 2,ceiling((row()-строка($ B $ 1)) / 5,1) -1,column()-столбец($B$1),1,COUNTA($ B $ 1: $ D $ 1)) в ячейке B2, и

=СМЕЩЕНИЕ(‘Форма Responses’!$A$2,ceiling((row()-row($B$1))/5,1)-1,mod(row()-(row($B$1) 1),5)*COUNTA($E$1:$N$1) COUNTA($B$1:$D$1),1,COUNTA($E$1:$N$1) ) в ячейке E2

Вставить значения: этот лист возвращает вставленные значения из обработанных строк, исключая формулу смещения, а затем удаляя все строки с пустыми ячейками E-N. Вот сценарий apps, отраженный на вкладке «Вставить значения»:

 var ss = SpreadsheetApp.getActive();
var sheet = SpreadsheetApp.getActiveSheet();

//Duplicate sheet 'Manipulated Rows' as paste values
function moveValuesOnly() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Paste Values');
  var source = ss.getRange('Manipulated Rows!A1:T100000');
  source.copyTo(ss.getRange('Paste Values!A1'), {contentsOnly: true});
  deleteRows(sheet);
}

//Function to Delete empty rows:
function deleteRows(sheet) {
  var rows = sheet.getDataRange();
  var range_manipulated_rows = ss.getSheetByName('Manipulated Rows!A1:T100000');
  var range_paste_values = ss.getSheetByName('Paste Values!A1:T100000');
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i  ) {
    var row = values[i];
    if (range_manipulated_rows == range_paste_values amp;amp; row[4] == '' amp;amp; row[5] == '' amp;amp; row[6] == '' amp;amp; row[7] == '' amp;amp; row[8] == '' amp;amp; row[9] == '' 
        amp;amp; row[10] == '' amp;amp; row[11] == '' amp;amp; row[12] == '' amp;amp; row[13] == '') { // if paste values tab is equal to manipulated rows tab and cell E-N are blank
      sheet.deleteRow((parseInt(i) 1) - rowsDeleted);
      rowsDeleted  ;
    }
  }
};
  

Я хочу сделать это более автоматизированным, создав скрипт apps, который напрямую преобразует лист «Ответы формы» в лист «Вставить значения» без использования измененных строк. Как и на листе «Вставить значения», необходимо удалить все строки, где все ячейки E-N пусты.

• Вы хотите напрямую преобразовать значения «Form Response» в «Paste Values» с помощью Google Apps Script.
• Существует 5 циклов от «Address» до «У вас есть другой запрос на принтер?» из столбцов от «D» до «AZ». Данные могут содержать 1 цикл и 3 цикла. Но максимум 5 циклов являются постоянными.

Из вашего вопроса и комментариев я смог понять выше. Как насчет этого примера скрипта?

Пример скрипта:

 function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var src = ss.getSheetByName("Form Responses");
  var dst = ss.getSheetByName("Paste Values");
  var values = src.getDataRange().getValues();
  var header = values.splice(0, 1)[0].splice(0, 13);
  var res = values.reduce(function(ar, e) {
    var h = e.splice(0, 3);
    h.unshift("");
    for (var i = 0; i < 5; i  ) {
      var temp = e.splice(0, 10);
      if (temp.filter(String).length == 0) continue;
      if (temp.length < 10) temp.splice(temp.length, 10 - temp.length, "");
      ar.push(h.concat(temp));
    }
    return ar;
  }, []);
  if (dst.getRange("A1").getValue() != "Status") res.unshift(["Status"].concat(header));
  dst.getRange(dst.getLastRow()   1, 1, res.length, res[0].length).setValues(res);
}
  

Примечание:

• В этом примере скрипта используются названия таблиц Form Responses и Paste Values . Если вы хотите изменить название листа, пожалуйста, измените скрипт.
• В этом примере скрипта строка заголовка листа Paste Values устанавливается автоматически. Если вы не хотите устанавливать это, пожалуйста, измените скрипт.

Ссылки:

• splice()
• уменьшить()
• concat()