I have the greatest trouble finding information about what I need. I guess I'm not looking in the right places since this is quite basic. Anyway.

Somebody somewhere has created a Google Sheet anyone with the link can access. I want to retrieve the value of a given cell.

I'd like to be able to do the following in JS from a simple web page (or server side in PHP, but preferably JS):

AGoogleApiIHopeExists.setApiKey("<MY_API_KEY");
var value = AGoogleApiIHopeExists
    .getSheet("AJEIDNNS6886SDHSKN67HBS7BA6SD555DSHD")
    .getTab("MyTabInsideMySheet")
    .getCell("B:17");

I'm afraid I'm a bit naive to think this is possible...

If not, where should I look for more info?

I have the greatest trouble finding information about what I need. I guess I'm not looking in the right places since this is quite basic. Anyway.

Somebody somewhere has created a Google Sheet anyone with the link can access. I want to retrieve the value of a given cell.

I'd like to be able to do the following in JS from a simple web page (or server side in PHP, but preferably JS):

AGoogleApiIHopeExists.setApiKey("<MY_API_KEY");
var value = AGoogleApiIHopeExists
    .getSheet("AJEIDNNS6886SDHSKN67HBS7BA6SD555DSHD")
    .getTab("MyTabInsideMySheet")
    .getCell("B:17");

I'm afraid I'm a bit naive to think this is possible...

If not, where should I look for more info?

• javascript
• php
• google-api
• google-sheets
• google-sheets-api

• 4 stackoverflow./questions/4143901/… – Jaya Vishwakarma Commented Nov 4, 2015 at 19:35

2 Answers 2

Try reading through the Google Sheets API. It is a REST API you can use to access & edit Google sheets. You do not need their client libraries (Java, .NET, etc.)

If the sheet is private, you will need to use OAuth 2.0 to gain access through a user account.

If the sheet is public, you will be able to access it without login. Note though that the sheet must still be published before you can access it.

I did some digging to help you out. Try this endpoint:

https://spreadsheets.google./feeds/cells/<sheetID>/default/public/full/R1C1?alt=json

R1C1 refers to Row 1, Column 1. You can change this as needed. default refers to the worksheet (tab) ID. If you need a different worksheet than the default, there are other API endpoints you can use to fetch the appropriate worksheet ID.

Here is a simple jsfiddle which allows you to enter a few variables and see the resulting value.

Dunno if this helps, but my experience with google sheets ended up using this... // the data returned looked like this:

{'version':'0.6','reqId':'0','status':'ok','sig':'1524270715','table':{'cols':[{'id':'A','label':'','type':'string'}],'rows':[{'c':[{'v':'A STORED URL HERE'}]}],'parsedNumHeaders':0}}

    let url2 = "https://docs.google./spreadsheets/d/<SHEET ID HERE>/gviz/tq?tqx=out:json&sheet=Data&gid=0&range=A2";



fetch(url2)
    .then(res  => res.text())
    .then(data => {
       data = data.substr(47).slice(0,-2);
       console.log("data_1: \n" + data);
       console.log("type_1: \n" + data.type);
       return data;
    })
    .then(info => {
         info = JSON.parse(info);
         console.log("info:  \n" + info); 
         console.log("GOT: \n" + info.table.rows[0].c[0].v); 
    })
    .catch(error => {console(error)
    });