'Exception: Service error: Spreadsheets when using Google AppScript
The following code fails on one file, at the arrow. The new file is created, but it fails to copy values. The same code works on a separate file, in exactly the same form. The code has not been touched in years, and it worked fine 4 weeks ago when last used, so it seems a bug on Google's part or something changed server side.
function copySheetValuesAndFormat(sourceName, destination, startData){
var source = SpreadsheetApp.getActive().getSheetByName(sourceName)
var sValues = source.getDataRange().getValues();
source.copyTo(destination).setName(sourceName)
var destinationSheet = destination.getSheetByName(sourceName)
---> destinationSheet.getRange(1,1,sValues.length,sValues[0].length).setValues(sValues)
checkRange = destinationSheet.getRange('D1:D')
lastRowWithData = getLastRowWithDataInARange(checkRange, startData)
lastRow = destinationSheet.getMaxRows()
destinationSheet.deleteRows(lastRowWithData + 1, lastRow - lastRowWithData - 1)
}
Solution 1:[1]
Address comments in code
function copySheetValuesAndFormat(sourceName, destination, startData){
var sh = SpreadsheetApp.getActive().getSheetByName(sourceName);
var vs = sh.getDataRange().getValues();
sh.copyTo(destination).setName(sourceName);//this should work if destination is in fact a range not just an A1 notation
var destinationSheet = destination.getSheetByName(sourceName)
destinationSheet.getRange(1,1,vs.length,vs[0].length).setValues(vs)
checkRange = destinationSheet.getRange('D1:D')//this is a bad way to assign a range
//try this instead destinationSheet.getRange('D1:D' ( destinationSheet.getLastRow()))
let lr = destinationSheet.getLastRow()
destinationSheet.deleteRows(lr + 1, destinationSheet.getMaxRows() - lr + 1);//not sure about this
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | MiMi |