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 Spre…

Accepted Answer

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

Service Error: Spreadsheets on Google Scripts

Solution

Problem