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

I have the same problem reason as ellockie mentioned: The dynamic range in a formula. In my case I was using a =INDEX(B7:B,1) to get the first cell after my column title, the cell B7. I was using this, because I use a sript to insert a new cell, shifting all the actual data down, so I need to get the first row value in other cell, no matter I move the cell. I change it for a =INDIRECT(CONCATENATE(CHARACTER(COLUMN()+64);ROW()+4)) to reference it dynamical

• CHARACTER(COLUMN()+64) give the column letter. Replacing =INDEX(B7:B,1) means B
• ROW()+4 give me the row number I want, 4 rows under I put this formula. replacing =INDEX(B7:B,1) means 7