How to script the addition of a row in one sheet when the source sheet has a row added?

Question

I have a google form which populates a google spreadsheet (source sheet). I located and customized a script to pull certain columns -in their entirety- from the source sheet into a new tab/sheet. Apparently there has to be the same number of rows in both sheets for the script to run properly. If not, it turns back an error. Every time a new form is submitted, a row is added to the source sheet, putting the two sheets out of sync and breaking the script.

I'd like help figuring out what function I need to add to the existing script (or what changes I can make to it) so when a new row appears in the source sheet (because a form has been submitted), a blank row appears in the target sheet.

Do I need to tweak my script or add a new function?

function importFunction(a) {
  var ss = SpreadsheetApp.openById("0ApTaY3v27-UqdElwZTBvanNpaC1UckpxaTJRZS1XNWc");
  var sourceSheet = ss.getSheets()[0];
  var ss2 = SpreadsheetApp.openById("0ApTaY3v27-UqdElwZTBvanNpaC1UckpxaTJRZS1XNWc");
  var targetSheet = ss2.getSheets()[1];
  var values1 = sourceSheet.setActiveSelection("C:C").getValues();
  var values2 = sourceSheet.setActiveSelection("AD:AD").getValues();
  var values3 = sourceSheet.setActiveSelection("D:E").getValues();
  var values4 = sourceSheet.setActiveSelection("AE:AE").getValues();
  var values5 = sourceSheet.setActiveSelection("F:H").getValues();
  var values6 = sourceSheet.setActiveSelection("N:U").getValues();
  targetSheet.setActiveSelection("A:A").setValues(values1);
  targetSheet.setActiveSelection("B:B").setValues(values2);
  targetSheet.setActiveSelection("C:D").setValues(values3);
  targetSheet.setActiveSelection("E:E").setValues(values4);
  targetSheet.setActiveSelection("F:H").setValues(values5);
  targetSheet.setActiveSelection("I:P").setValues(values6);
}

Per the suggestion below, I tried to change script to the following, but I get an error - Cannot find method appendRow(). How do I fix that?

    function importFunction(a) {
      var ss = SpreadsheetApp.openById("0ApTaY3v27-UqdElwZTBvanNpaC1UckpxaTJRZS1XNWc");
      var sourceSheet = ss.getSheets()[0];
      var ss2 = SpreadsheetApp.openById("0ApTaY3v27-UqdElwZTBvanNpaC1UckpxaTJRZS1XNWc");
      var targetSheet = ss2.getSheets()[1];
      var targetMax = targetSheet.getMaxRows();
      var values1 = sourceSheet.setActiveSelection("C:C").getValues();
      var values2 = sourceSheet.setActiveSelection("AD:AD").getValues();
      var values3 = sourceSheet.setActiveSelection("D:E").getValues();
      var values4 = sourceSheet.setActiveSelection("AE:AE").getValues();
      var values5 = sourceSheet.setActiveSelection("F:H").getValues();
      var values6 = sourceSheet.setActiveSelection("N:U").getValues();
      if(targetMax == values1.length) {
        targetSheet.setActiveSelection("A:A").setValues(values1);
        targetSheet.setActiveSelection("B:B").setValues(values2);
        targetSheet.setActiveSelection("C:D").setValues(values3);
        targetSheet.setActiveSelection("E:E").setValues(values4);
        targetSheet.setActiveSelection("F:H").setValues(values5);
        targetSheet.setActiveSelection("I:P").setValues(values6);
      }
      else
        targetSheet.appendRow();
    }

the method appendRow([rowcontent]) needs an argument... but nevertheless it was not the right solution, please see edit — Serge insas

Serge insas Serge insas · Accepted Answer · 2012-08-30T21:25:45

You could simply check the number of available rows in sheet SS2 before writing the values using getMaxRows() and compare it to the length of your data arrays value1.length, then, depending on this comparison add the rows you need using appendRow() eventually in a for next loop.

EDIT : following your EDIT, I tested a bit further and it appears that appendRow() doesn't work as I thought, it appends Rows at the begining of an empty sheet... so I tried this :

  if(targetMax < values1.length) {
  var RowsToAdd = values1.length-targetMax
  for(nn=0;nn<RowsToAdd;++nn){targetSheet.insertRowAfter(targetMax)}
  }

Just place it right after var value6=...

sorry for this little error ;-)

How to script the addition of a row in one sheet when the source sheet has a row added?

1 Answers