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

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});
  }
}