My goal is to transfer data from one spreadsheet to another. The code should ideally retrieve data from one spreadsheet, save it into a 2D array, and then print all the data into another spreadsheet. However, I am encountering an error during the printing process that reads "Exception: Service error: Spreadsheets."
var pasteSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
uploadFolder = DriveApp.getFolderById('someID')
obtainAndImportData(uploadFolder)
function obtainAndImportData(uploadFolder){
try{
var internalFiles = uploadFolder.getFiles()
} catch{
return
}
while (internalFiles.hasNext()){
try{
var file = internalFiles.next()
} catch {
break
}
var fileID = file.getId()
var copySheet = SpreadsheetApp.openById(fileID).getSheets()[0]
var Cvals = copySheet.getRange("C1:C" + copySheet.getLastRow()).getValues()
var ldr = Cvals.length;
var Csheet = pasteSheet.getRange("C1:C" + pasteSheet.getLastRow()).getValues()
var lstv = Csheet.length;
var allRows = []
for (i = 0;i < ldr;i++){
allRows.push(copySheet.getRange(`B${i + 3}:P${i + 3}`).getValues()[0])
}
console.log(allRows)
var rangeToUnify = pasteSheet.getRange(lstv + 1,1,allRows.length,allRows[0].length)
rangeToUnify.setValues(allRows) // this line throws the error
}
}
I have researched that this issue may be related to large amounts of data, but even testing with just one line causes the same failure.
The data consists of columns from A to P and any number of rows. It's worth mentioning that the original file format is ".xslx," which is later converted to a Google Sheets file. https://i.sstatic.net/3e2AV.png