CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Lauren
Lauren

Reputation: 49

Sheets Forms Data Manipulation and Deleting Rows if Certain Cells are Blank

This question is similar to "Forms Data Manipulation In Google Sheets" (https://webapps.stackexchange.com/questions/88736/forms-data-manipulation-in-google-sheets) but requires a bit more automation:

Background: Users fill out a google form for a request and have the option of repeating those same questions to fill out a second, third, fourth, and fifth request. I have created a sheet that will manipulate these rows so that rows with identical columns will be transferred to one column.

Here is my example sheet: https://docs.google.com/spreadsheets/d/11DM7z_vwuR1S6lgMN7Wu7a0GoouVc2_5xj6nZ1Ozj5I/edit#gid=1967901028

Form Responses: sheet that returns the responses from users filling out form

Manipulated Rows: sheet that returns manipulated rows using: =OFFSET('Form Responses'!$A$2,ceiling((row()-row($B$1))/5,1)-1,column()-column($B$1),1,COUNTA($B$1:$D$1)) in cell B2, and

=OFFSET('Form 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)) in cell E2

Paste Values: this sheet returns a paste values of Manipulated Rows, excluding the Offset formula and then deleting any rows that have blank cells E-N. Here is the apps script reflected in the 'Paste Values' tab:

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 && row[4] == '' && row[5] == '' && row[6] == '' && row[7] == '' && row[8] == '' && row[9] == '' 
        && row[10] == '' && row[11] == '' && row[12] == '' && 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++;
    }
  }
};

I want to make this more automated by creating an apps script that will directly convert the sheet of 'Form Responses' to the sheet of 'Paste Values' without using Manipulated Rows. As in the 'Paste Values' sheet, it needs to remove any rows where all of cells E-N are blank.

Upvotes: 0

Views: 988

Answers (1)

Tanaike
Tanaike

Reputation: 201338

  • You want to directly convert the values of "Form Response" to "Paste Values" using Google Apps Script.
  • There are 5 cycles of "Address" to "Do you have another printer request?" of the columns of "D" to "AZ". The data might be 1 cycle and 3 cycles. But The maximum 5 cycles are constant.

From your question and comments, I could understand above. How about this sample script?

Sample script:

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);
}

Note:

  • In this sample script, the sheet names of Form Responses and Paste Values are used. If you want to change the sheet name, please modify the script.
  • In this sample script, the header row of the sheet of Paste Values is automatically set. If you don't want to set this, please modify the script.

References:

Upvotes: 2

Related Questions