Problem with getting JSON data from Google Sheets

i'm having an error when i'm trying to get results from my google sheet
this is my error

this is my doGet Script
when i'm trying to deploy the web app
i can see the results on my browser

and those are my blocks

Your script url doesn't look right, should be like this:

and what web block url should be?
the same as the url script?

set Web2.Url

i've set the web2 url block server address to the url format you mention with /exec extension
also i've changed in my script the following

function doGet() {
//get data from sheet
var ss = SpreadsheetApp.openById("1333t7lvECnmOcCnTE6gnn_RN1wrPckWpIETiPUjk");
var sh = ss.getSheetByName("prices");
var values = JSON.stringify(sh.getDataRange().getValues());
return ContentService.createTextOutput(values);

still getting this error
even though i'm changing my script url to the url with the /exec extension

var ss = SpreadsheetApp.openByUrl("");

1 Open the script project
2 Go to Publish
3 Deploy as Web App
4 Project version: - select New from the dropdown
5 Execute the app as: your google account address (email)
6 Who has access to the app: Anyone, even anonymous
7 Press the Update button

You have to do this EVERY TIME you change your script

What is this?

var ss = SpreadsheetApp.openByUrl("");

Why have your set the spreadsheet url to the script url ?

Did no one notice that you are sending your resposecontent through two consecutive JSONTextdecode transforms?

i follow Exactly your Instructions
now i'm getting a new error

so how it should be?

Just one....

Can you show the output of your response Content?


i followed your guide for the blocks and for the script here

which means, your list is not a list, but the text "not"

Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by Taifun.

That was a particularly tricky case. You data is more straight forward.

Assuming you are getting back:

[["id","name"], [130,170],[10,250],[35,370]......]

You are getting a json array of arrays.

first of all thank you for your kind help and time
now its working :wink:

but i have a small question for you
what if i want to type a another number in my json array (key) instead of 10
for example to put in my app textbox to put the number and get the pair result?
how do i do that?

I have done it two ways for you, first with a textbox, where the user types an id number (requires error checking for id numbers that do not exist though), and secondly with a spinner list of all the ids (note if you try to select an item in a spinner list that is already selected, the afterSelecting event will not fire)

1 Like

Great thank you very much

You can also follow the next code, i used it to an unfinished location share app that stores things on google sheets and then the other user should receive a sms , etc. you can see the following code:



Google app script

function addSheetDataRow(e, sheet){
var PhoneNumber = e.parameter.PhoneNumber
var ToShare = e.parameter.ToShare
var Action = e.parameter.Action
var Latitude = e.parameter.Latitude
var Longitude = e.parameter.Longitude
var Altitude = e.parameter.Altitude
var Adress = e.parameter.Adress
sheet.appendRow([PhoneNumber, ToShare, Action, Latitude, Longitude, Altitude, Adress])
function doGet (e) {
var ss = SpreadsheetApp.openByUrl(" ");
addSheetDataRow (e, ss);
function doPost (e) {
var ss = SpreadsheetApp.openByUrl("");
addSheetDataRow(e, ss);

1 Like

thank you for your help

It is just a second way :sunglasses: