Service error: spreadsheets in Google scripts

Question

Region

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

What's happening:

As soon as I debug the script code, it works fine, without any serious problems, but as soon as I run it from the spreadsheet itself (from the menu I created), it performs several script steps and produces a: Service Error: Spreadsheet without any other details.

Weirdness

I started to “register” the current stage of the process in a table cell, so that I could track its progress when running the script from the debugger.

The problem is that when I move some "random" fragments, such as:

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

code tends to break up at different points.

Code example:

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

All you have to do is:

1 - Create a new spreadsheet in Google Drive

2 - Hit Tools -> Script Editor

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

4 - Reload the table (F5) so that the user menu "Guild Wars 2 Tracker" now appears

5 - Click the button and click "List All"

Desired Result:

What this code should (if not for this error) :

1 - Fulfill the request at 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 - Iterate over each page, parse json and write return values ​​to table

Denial of responsibility:

Sorry for all the "Journal" posts on the sheet. It was a desperate attempt to track my progress, I know that I should not do this.

Thank you in advance

Update 1:

After creating another table and pasting pastebin code into my own script project, I can run it for interaction, but that’s all. This time he raised another error: We're sorry, a server error occurred. Please wait a bit and try again. We're sorry, a server error occurred. Please wait a bit and try again.

Answer 1

I like what ellockie said - I (involuntarily) had the same problem. I tried to use range.sort (8), but to collect the range I used:

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

But I had to use:

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

The error message from 5/1/2015 is still very mysterious and does not contain any additional information other than "Service error: tables."

Answer 2

Such service errors may occur when accessing the wrong ranges, and the error may not occur until subsequent access. For example, if you get a Range that refers to non-existent columns (for example, H if you have only AE) or non-existent rows (for example, for row 10001 if you have only 10,000 rows). This is noted in the application script problem tracker: https://issuetracker.google.com/issues/68062620

As for the source of your problem, your script is highly non-optimized and does not follow the Apps "Best Practices" script regarding the use of the spreadsheet service. Namely, you should use batch operations, such as the Range#setValues , to write whole blocks, or at least appendRow to add each row (instead of sheet.getRange(rowIndex, someColumn).setValue(oneValue) ). These methods will add the appropriate rows to store data if they should.

An example of changing 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 3

Marcello, today I ran into the same problem and just found an answer to get around it here: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3815 (see # 4). The idea is to add lines to the bottom of the sheet that will allow the scripts to start working again. This works in my case.

Answer 4

In my case, I had formulas with "dynamic" ranges, i.e. =sum(b2:b) , which, as I recall, was mentioned as random problems in the new Google spreadsheets.

Fixing to sum(b2:b22) (make sure the range does not exceed the last sheet of the sheet) solves the problem.

Answer 5

In my case, I get this error because I asked another table to sort the data with a range of 25 columns when there were only 19 in the spreadsheet.

In my wisdom, I thought that I would clean the table by deleting unnecessary columns after I wrote a script to sort 25 columns instead of using getLastColumn - don't ask me why.

Answer 6

I have the same problem as ellocs: dynamic range in the formula. In my case, I used =INDEX(B7:B,1) to get the first cell after my column header, cell B7. I used this because I use sript to insert a new cell, moving all the actual data down, so I need to get the first row value in another cell, regardless of whether I move the cell. I change it to =INDIRECT(CONCATENATE(CHARACTER(COLUMN()+64);ROW()+4)) to refer to it dynamically

• CHARACTER(COLUMN()+64) specify the letter of the column. Replacement =INDEX(B7:B,1) means B
• ROW()+4 give me the line number I want, 4 lines under this formula. replacing =INDEX(B7:B,1) means 7

Answer 7

I had the same thing. But they found out the reason that tried to copy one cell ... CAN YOU BELIEVE?

part of the script tried to clear one line, and that damn cell was there. I tried to copy one row and the whole spreadsheet crashed. restart and continue by copying the cells one by one and finding out that the empty cell is causing a failure. Deleted the entire column and all the other empty columns ... then crossed the heavenly gate!

Now you can copy the entire line and the script works fine !!!!!