Service Error Spreadsheet in App Script when trying to import data from one spreadsheet to another

Question

I am trying to import data from one spreadsheet to another. Ideally, this code should get data from one spreadsheet, store it into a 2d array, then print all this data into another spreadsheet. The process that involves printing the values is the one that fails and throws the error 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 read this might be due to big amounts of data, but I have tested with only one line, yet it keeps failing.

Data has columns from A to P and no specific number of rows. Maybe it is important to note that the file(s) from which I want the information, is originally ".xslx" but then is opened as Google sheets file.

Thanks for your reply but the edit you just made is not enough to clarify how the the function is being "called". Are you running the function from the Apps Script editor? By the way, while links to external content might be helpful, questions on this site should be self contained. Considering this, please add a brief description on the relevant parts of your spreadsheet and some sample data directly into the question body. — Rubén, Sep 05 '22 at 16:08

Rubén · Answer 1 · 2022-09-05T16:22:23.877

As you are running obtainAndImportData from the Apps Script editor, remove obtainAndImportData(uploadFolder) from the global scope. This because calling a function from the global scope makes the function be executed every time that any function is run. In this case your function will run twice, one when the runtime loads the code, and the second when it executed the function selected in the Apps Script editor dropdown.

Move the variable declarations that call Google Apps Script services from the global scope, i.e., move

var pasteSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
uploadFolder = DriveApp.getFolderById('someID')

just below

function obtainAndImportData(uploadFolder){

What is the scope of variables in JavaScript?

score 0 · Accepted Answer · answered Sep 09 '22 at 14:37

For anyone who is interested in the answer, I found out this error always appeared whenever the program found an image in a cell. Images are objects and cannot be printed from one cell into another.

An alternative would be to ignore cells where this error happens, and just have number and string values be transferred between spreadsheets. Here's my way to do it:

    for (i = 0; i < allRows.length; i++){ // Replace allRows with a 2d array
      for (j = 0; j < allRows[i].length; j++){
        var cellType = typeof allRows[i][j]
        if (cellType == "object"){
          allRows[i].splice(j,1,"Object type element")
          }
      }
    }

Service Error Spreadsheet in App Script when trying to import data from one spreadsheet to another

2 Answers2