How to access cell values from a Google Sheet?

Question

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?

Answer 1

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.com/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.