How can I access Google Sheet spreadsheets only with Javascript?

Question

I want to access Google Spreadsheets using JavaScript only (no .NET, C#, Java, etc.)

I came here and was shocked to know that there is NO API for JavaScript to access Google Sheets.

Please tell me how to access (CREATE/EDIT/DELETE) Google Sheets using JavaScript or any of its frameworks like jQuery.

Solution 1

I have created a simple javascript library that retrieves google spreadsheet data (if they are published) via the JSON api:

https://github.com/mikeymckay/google-spreadsheet-javascript

You can see it in action here:

http://mikeymckay.github.com/google-spreadsheet-javascript/sample.html

Solution 2

Jan 2018 UPDATE: When I answered this question last year, I neglected to mention a third way to access Google APIs with JavaScript, and that would be from Node.js apps using its client library, so I added it below.

It's Mar 2017, and most of the answers here are outdated -- the accepted answer now refers to a library that uses an older API version. A more current answer: you can access most Google APIs with JavaScript only. Google provides 2 (correction, 3) ways to do this today:

1. As mentioned in the answer by Dan Dascalescu, you can use Google Apps Script, the JavaScript-in-Google's-cloud solution. That is, non-Node server-side JS apps outside the browser that run on Google servers.

• You code your apps in the Apps Script code editor, and they can access Google Sheets in two different ways:
  • The Spreadsheet Service (native object support; usage guide); native is easier but is generally older than...
  • The Google Sheets Advanced Service (directly access the latest Google Sheets REST API [see below]; usage guide)
• Apps Script also powers add-ons, and you can extend Sheets UI functionality with Sheets add-ons (like these)
• You can even write mobile add-ons which extend the Sheets app on Android
• To learn more about using Apps Script, check out these videos I've created (most involve the use of Sheets)

2. You can also use the Google APIs Client Library for JavaScript to access the latest Google Sheets REST API on the client side.

• Here are some generic samples of using the client library
• The latest Sheets API (v4) was released at Google I/O 2016; it's much more powerful than all previous versions, giving developers programmatic access to most features found in the Sheets UI
• Here is the JavaScript quickstart for the API to help you get started
• Here are sample "recipes" (JSON payloads) for core API requests
• If you're not "allergic" to Python (if you are, just pretend it's pseudocode ;) ), I made several videos with more "real-world" samples of using the API you can learn from and migrate to JS if desired (NOTE: even though it's Python code, most API requests have JSON & easily portable to JS):
  1. Migrating SQL data to a Sheet (code deep dive post)
  2. Formatting text using the Sheets API (code deep dive post)
  3. Generating slides from spreadsheet data (code deep dive post)
  4. Those and others in the Sheets API video library

3. The 3rd way to access Google APIs with JavaScript is from the Node.js client library on the server-side. It works similarly to using the JavaScript (client) client library described just above, only you'll be accessing the same API from the server-side. Here's the Node.js Quickstart example for Sheets. You may find the Python-based videos above to be even more useful as they too access the API from the server-side.

When using the REST API, you need to manage & store your source code as well as perform authorization by rolling your own auth code (see samples above). Apps Script handles this on your behalf, managing the data (reducing the "pain" as mentioned by Ape-inago in their answer), and your code is stored on Google's servers. But your functionality is restricted to what services App Script provides whereas the REST API gives developers much broader access to the API. But hey, it's good to have choices, right? In summary, to answer the OP original question, instead of zero, developers have three ways of accessing Google Sheets using JavaScript.

Solution 3

Here's the Gist.

You can create a spreadsheet using the Google Sheets API. There is currently no way to delete a spreadsheet using the API (read the documentation). Think of Google Docs API as the route to create and look-up documents.

You can add/remove worksheets within the spreadsheet using the worksheet based feeds.

Updating a spreadsheet is done through either list based feeds or cell based feeds.

Reading the spreadsheet can be done through either the Google Spreadsheets APIs mentioned above or, for published sheets only, by using the Google Visualization API Query Language to query the data (which can return results in CSV, JSON, or HTML table format).

---

Forget jQuery. jQuery is only really valuable if you're traversing the DOM. Since GAS (Google Apps Scripting) doesn't use the DOM jQuery will add no value to your code. Stick to vanilla.

I'm really surprised that nobody has provided this information in an answer yet. Not only can it be done, but it's relatively easy to do using vanilla JS. The only exception being the Google Visualization API which is relatively new (as of 2011). The Visualization API also works exclusively through a HTTP query string URI.

Solution 4

There's a solution that does not require one to publish the spreadsheet. However, the sheet does need to be 'Shared'. More specifically, one needs to share the sheet in a manner where anyone with the link can access the spreadsheet. Once this is done, one can use the Google Sheets HTTP API.

First up, you need an Google API key. Head here: https://developers.google.com/places/web-service/get-api-key NB. Please be aware of the security ramifications of having an API key made available to the public: https://support.google.com/googleapi/answer/6310037

Get all data for a spreadsheet - warning, this can be a lot of data.

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/?key={yourAPIKey}&includeGridData=true

Get sheet metadata

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/?key={yourAPIKey}

Get a range of cells

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{sheetName}!{cellRange}?key={yourAPIKey}

Now armed with this information, one can use AJAX to retrieve data and then manipulate it in JavaScript. I would recommend using axios.

var url = "https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/?key={yourAPIKey}&includeGridData=true";                                                             
axios.get(url)
  .then(function (response) {
    console.log(response);                                                                                                                                                    
  })
  .catch(function (error) {
    console.log(error);                                                                                                                                                       
  });                

Solution 5

2016 update: The easiest way is to use the Google Apps Script API, in particular the SpreadSheet Service. This works for private sheets, unlike the other answers that require the spreadsheet to be published.

This will let you bind JavaScript code to a Google Sheet, and execute it when the sheet is opened, or when a menu item (that you can define) is selected.

Here's a Quickstart/Demo. The code looks like this:

// Let's say you have a sheet of First, Last, email and you want to return the email of the
// row the user has placed the cursor on.
function getActiveEmail() {
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var activeRow = .getActiveCell().getRow();
  var email = activeSheet.getRange(activeRow, 3).getValue();

  return email;
}

You can also publish such scripts as web apps.

View more solutions

Comments

[GSto]

the link you provided has information about using JSON. you should be able to use that in JavaScript.

[Pratik]

@GSto it would be helpful if u can provide me some insight to this.let me tel u again i want to access google spreadsheet via javascript.Thanks.

[FONGOH MARTIN]

This is the full tutorial developers.google.com/sheets/api/quickstart/js

[Jester]

Might want to consider using Sheetsu. It's fairly simple for JSON API and limited with the free version, but it really simplifies what any dev should need for using Google spreadsheets. Hope this helps some people.

[Pratik]

why u call it as "pain".whats harm in doing so ?

[Ape-inago]

A pain because it doesn't have a native api, which means you need to do a lot of parsing and data manipulation yourself. a native spreadsheet api would provide these for you.

[Ryan Weiss]

This saved me a ton of time. Thank you so much! I did, however, decide to fork your code, to be able to handle empty cells of data, and also organize it into rows (the data being returned as is is just a giant array of cells, but since empty cells weren't being taken into account, there was no easy way to organize the data). The update was just to your 'callbackCells()' method. Check it out: github.com/rw3iss/google-spreadsheet-javascript.git Thanks again man!

[Ape-inago]

Evan Plaice's answer stackoverflow.com/a/8666573/42082 has more detailed information about the official Google Docs API and how to use the spreadsheet one. Worth a look.

[vladkras]

link 1 will be deprecated On April 20, 2015, link 2 404 error, link 3 v2.0 deprecated, link 4 deprecated

[PreguntonCojoneroCabrón]

new version API 3.0 and Oauth 2.0

[Dan Dascalescu]

The OP asked about Google Sheets specifically, which has a more specific API than Drive.

[vladkras]

ok, here is the full list of Google products including spreadsheet (easy to find using my first link though)

[A_01]

Not a good idea. Google is going to shut it down. Downvoting it.

[William Entriken]

What is grid data?

[robsco]

@WilliamEntriken The contents of the cells.

[Richard]

And now there's a PHP version of the import utility - available at the same URL

[Chirag]

Looks like google changed policy and this is broken now.