I have this script that copy datas from cells A2:D4 to the last row in the destination sheet. The problem is that in the destination sheet , in column E, I have set a formula for the entire column so when I run the script all the datas are copied at the end of my destination sheet. How can I do so that the script ignores any formula in another column?

function Movedata() {
      var ss = SpreadsheetApp.getActive();
      var sh = ss.getSheetByName("Fatture");
      var range = sh.getRange("A2:D4");
      var values = range.getValues();
      var dsh = ss.getSheetByName("Elenco");
      dsh.getRange(dsh.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
      };

I have this script that copy datas from cells A2:D4 to the last row in the destination sheet. The problem is that in the destination sheet , in column E, I have set a formula for the entire column so when I run the script all the datas are copied at the end of my destination sheet. How can I do so that the script ignores any formula in another column?

function Movedata() {
      var ss = SpreadsheetApp.getActive();
      var sh = ss.getSheetByName("Fatture");
      var range = sh.getRange("A2:D4");
      var values = range.getValues();
      var dsh = ss.getSheetByName("Elenco");
      dsh.getRange(dsh.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
      };

• google-sheets
• google-apps-script

• What formula do you have? Possible duplicate: stackoverflow/questions/46883862/… – TheMaster Commented Feb 15 at 5:00

2 Answers 2

Use the getLastRow_() utility function, like this:

  dsh.getRange(getLastRow_(dsh, 'A:D') + 1, 1, values.length, values[0].length).setValues(values);

getLastRow_() gets the last row that has visible content. If your formulas return blank values à la iferror(ø), or zero-length text strings à la "", they will be ignored, and the last row with visible content is returned instead.

Alternatively, use the appendRows_() utility function, like this:

function Movedata() {
  const ss = SpreadsheetApp.getActive();
  const values = ss.getRange('Fatture!A2:D4').getValues();
  const dsh = ss.getSheetByName('Elenco');
  appendRows_(dsh, values, 'A:D');
}

Alternative Answer

The script retrieves all values from the output sheet Elenco, then filters out empty rows.

Without the length property, the variable last becomes an array instead of a number which will cause an error since getRange() requires a number representing a row index.

Working Code:

function Movedata() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("Fatture");
  var range = sh.getRange("A2:D4");
  var values = range.getValues();
  var dsh = ss.getSheetByName("Elenco");
  var last = dsh.getRange("A:A").getValues().filter(x => x[0]).length;
  dsh.getRange(last + 1, 1, values.length, values[0].length).setValues(values);
  };

Example Output:

Fatture (this data is in the range A2:D4):

Elenco (After running the script one time):

Elenco (after running the script two times):

References:

• Array.prototype.filter()
• Array: length