CODEWITHSUNDEEP
google-apps-script
djreed
djreed

Reputation: 2733

Copy values only from one sheet to another

I have a sheet with an active filter and I would essentially like to delete anything but the filtered data since there are thousands of other irrelevant rows behind the filter.

As a solution, I was planning on copy-pasting the range of value of the filtered data to a clean sheet:

function formatUserSheet_(id, sheet) {
  var ss = SpreadsheetApp.openById(id);
  var sheetToCopyFrom = ss.getSheets()[1];
  var sheetToCopyTo = ss.getSheets()[0];
  sheetToCopyFrom.getRange("AT:BH").copyTo(sheetToCopyTo.getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES);
}

Unfortunately the result is almost perfect except returns #REF! errors because it is carrying over formulas, not just values.

Upvotes: 1

Views: 3447

Answers (1)

Liora Haydont
Liora Haydont

Reputation: 1283

The function you want to use is copyTo(destination, copyPasteType, transposed) with 3 arguments. If you only put 2 arguments, it's using copyTo(destination, options), so the paste type is the default one.

If you add false at the end of the function for the parameter transposed, it will copy the values correctly.

sheetToCopyFrom.getRange("AT:BH").copyTo(sheetToCopyTo.getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

For some reason the example only use 2 parameters, I guess it must have changed and they didn't update the example...

Upvotes: 3

Related Questions