Service Error: Spreadsheets on Google Scripts

Question

Scope

I started to write an script that will make chained calls to an API (with a JSON response) and write the result into a Spreadsheet.

What Happens:

Once i debug the script code, it runs just fine, with no major problem, but once i run it from the Spreadsheet button itself (from the menu i created) it runs some steps of the script and than, pops a : Service Error: Spreadsheet with no other error details.

Weirdness

I Started to "Log" the current step of the process to a Spreadsheet cell, so that i can monitor its progress while running the script out of the debugger.

The problem is, once i move some "random" pieces such as :

sheet.getRange("F2").setValue(currentPage);

the code tends to break in different points.

Code Sample :

You can find a code to reproduce the issue here : http://pastebin.com/HjmSwEYZ

All you have to do is :

1 - Create a new Spreadsheet on Google Drive

2 - Hit Tools -> Script Editor

3 - Create a new Script, paste the code in, and save

4 - Reload the Spreadsheet (F5) so that the custom menu will now appear "Guild Wars 2 Tracker"

5 - Click the button and hit "List All"

Desired Output:

What this code should (if it wasn't for this error) do is :

1 - Execute a request on this url : http://www.gw2spidy.com/api/v0.9/json/items/all/1 (which will return the first page of Guild Wars 2 itens)

2 - Iterates over each page, parsing the json and writing the returned values into the Spreadsheet

Disclaimer:

Sorry about all the "Log" messages in the sheet. This was a desperate attempt to track my progress, i know i should't do this.

Thanks in advance

Update 1:

After creating another Spreadsheet and pasting the pastebin code in its own Script Project i could run it for on interaction, but that was it. This time thus, it raised a different error : We're sorry, a server error occurred. Please wait a bit and try again.

Answer 1

Marchello, I've run into the same problem today and have just found an answer to get round it here: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3815 (see #4). The idea is to add rows to the bottom of the sheet that would let the scripts start working again. It does work in my case.

Answer 2

In my case I had formulas with 'dynamic' ranges, i.e. =sum(b2:b), which I remember was mentioned as a casuse of issues in the new google spreadsheets.

Correcting to sum(b2:b22) (making sure the range doesn't exceed the sheet's last row) solved the problem.

Answer 3

I like what ellockie said - I was (unwittingly) having the same problem. I was trying to range.sort(8), but to collect the range, I used:

sheet.getRange(2,1,sheet.getMaxRows(), sheet.getMaxColumns());

But what I should have used was:

sheet.getRange(2, 1, sheet.getMaxRows()-1, sheet.getMaxColumns());

The error message, as of 5/1/2015, still is very cryptic and offers no further details than the "Service error: Spreadsheets".

Answer 4

In my case I get this error because I was asking another spreadsheet to sort it's data with a range of 25 columns when there were only 19 on the spreadsheet.

In my wisdom I thought I would tidy up the spreadsheet by deleting un-needed columns after I had written the script to sort 25 columns instead of using getLastColumn - don't ask me why.

Answer 5

You can get service errors like this when you access improper ranges, and the error may not be raised until a subsequent access. For example, if you get a Range that references columns that don't exist (like H if you only have A-E), or rows that don't exist (like row 10001 when you only have 10000 rows). This is noted in the Apps Script Issue Tracker: https://issuetracker.google.com/issues/68062620

With regards to the source of your issue, your script is highly unoptimized and does not follow Apps Script "Best Practices" regarding use of the Spreadsheet Service. Namely, you should use batch operations such as Range#setValues operation to write whole blocks, or at least appendRow to append each row (instead of sheet.getRange(rowIndex, someColumn).setValue(oneValue)). These methods will add relevant rows to hold the data if they need to.

An example modification of your code:

var itemFields = [ "name",
                   "rarity",
                   "price_last_changed",
                   "max_offer_unit_price",
                   "min_sale_unit_price",
                   "offer_availability",
                   "sale_availability" ];
function addResultPage_(sheet, results) {
  const imgs = [],
  const data = results.map(function (result, index) {
    if (result.img)
      imgs.push({row: index, url: result.img});
    return itemFields.map(function (field) { return result[field] || ""; });
  });
  if (!data.length) return;

  const startRow = sheet.getLastRow() + 1;
  sheet.getRange(startRow, 2, data.length, data[0].length).setValues(data);
  if (imgs.length)
    imgs.forEach(function (imgInfo) {
      sheet.insertImage(imgInfo.url, 1, startRow + imgInfo.row);
    });
}
function listAllItems() {
  const sheet = SpreadsheetApp.getActiveSheet(),
        totalPages = updateStartStatus("List All Items");

  for (var page = 1; page <= totalPages; ++page) {
    var pageResults = getItemsByPage(page);
    if (pageResults.results)
      addResultPage_(sheet, pageResults.results);
    else
      console.warn({message: "No results for page '" + page + "'", resp: pageResults});
  }
}

Answer 6

I had the same. But figured out the root cause, which was trying to copy a single cell... CAN YOU BELIEVE IT?

part of script was trying to clear a single row, and that damn cell was there. I was trying to copy a single row and the whole Spreadsheet crashes. restarted and continued by copying cells one by one and figured out that an empty cell causes the crash. Deleted the entire column and all other empty columns... then crossed through the heaven's gate!

now, can copy entire row and script works fine!!!!!