How to access Google Sheet spreadsheets using Javascript only?

Question

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

I came here and was shocked to know that there is no JavaScript for JavaScript to access the Google spreadsheet.

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

Answer 1

I created a simple javascript library that retrieves google spreadsheet data (if 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

Answer 2

Gist here.

You can create a spreadsheet using the Google Sheets API . There is currently no way to delete a table using the API (see Documentation). Think of the Google Docs API as the route to create and search for documents.

You can add / remove worksheets in a spreadsheet using worksheet-based sheets .

Spreadsheet updates are performed through lists based on a list or cell-based feeds .

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

---

Forget jQuery. jQuery is really very valuable if you cross the DOM. Since GAS (Scripting) does not use the DOM, jQuery will not add any value to your code. Stick to vanilla.

I am really surprised that no one provided this information in the answer. Not only can this be done, but it is relatively easy to do using vanilla JS. The one exception is the Google Visualization API, which is relatively new (since 2011). The rendering API also works exclusively through the HTTP request string URI.

Answer 3

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

It's Mar 2017 , and most of the answers here are out of date - the accepted answer now refers to a library that uses an older version of the API. More relevant answer: you can access most of the Google APIs only using JavaScript. Google offers 3 ways to do it today:

• As mentioned in Dan Daskalescu's answer , you can use Google Apps Script , a JavaScript solution on Google. That is, non-w631> server-side JS applications outside the browser that run on Google servers.
  • You encode your applications into the Applications Script code editor , and they can access Google Sheets in two different ways:
    • Spreadsheet service (support for native objects usage guide ); native is lighter, but usually older than ...
    • advanced Google Sheets service (directly access the latest Google Sheets REST API [see below]; usage guide )
  • Script applications also support add-ons , and you can extend the Sheets UI functionality with sheet add-ons (like these )
  • You can even write mobile add-ons that extend Android Application Sheets
  • To learn more about using Script applications, check out these videos that I created (most of them are related to using tables)
• You can also use the Google APIs API Client Library for JavaScript to access the latest Google Lets REST APIs on the client side.
  • Below are some common samples using the client library
  • In the last sheet, API (v4) was released in Google I / O 2016; it is much more powerful than all previous versions, giving developers programmatic access to most of the features found in the Sheets user interface
  • Below is a quick JavaScript launch for the API to help you get started
  • sample recipes (JSON payloads) for core API requests
  • If you are not “allergic” to Python (if you want, just pretend it's a pseudo-code;)), I made some videos with more “real” examples of using the API with which you can learn and switch to JS if necessary ( NOTE: Although this is Python code, most API requests have JSON and are easily portable to JS):
    • Migrate SQL data in a worksheet (deep dive post code)
    • Text formatting using table APIs (deep dive with post text)
    • Creating slides from spreadsheet data (deep dive post code)
• The third way to access the Google APIs using JavaScript is through Node.js applications using your client library . It works similarly to using the JavaScript client library described above, only you will get access to the same API from the server side. Here's an example of Node.js Quickstart for sheets. You can find the Python-based videos above to be even more useful as they also access the API from the server side.

When using the REST API, you need to manage and store the source code, as well as authorize by copying your own auth code (see examples above). Script applications handle this on your behalf, managing the data (reducing the “pain” as Ape-inago mentioned in their answer ), and your code is stored on Google servers. But your functionality is limited by the capabilities provided by App Script services and older JS (ES3 + some ES5 features and Google settings), while the REST API provides developers with much wider access to the API. But hey, it's good to have a choice, isn't it? So, to answer the original OP question, instead of zero, developers have three ways to access Google Sheets using JavaScript.

Answer 4

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

This will allow you to bind JavaScript code to a Google worksheet and execute it when you open the worksheet or when a menu item (which you can define) is selected.

Here's a quick start / demo . The code is as follows:

// Let 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 scripts such as web applications .

Answer 5

edit:. This was answered before the google doc api was released. See Evan Place's answer and Dan Daskalescu's answer for more up to date information.

Sounds like you can, but it's a pain to use. It includes the use of the Google Data API.

http://gdatatips.blogspot.com/2008/12/using-javascript-client-library-w-non.html

"The JavaScript client library has helper methods for Calendar, Contacts, Blogger, and Google Finance. However, you can use it with almost any Google data API to access authenticated / private feeds. This example uses the DocList API."

and an example of writing a gadget that interacts with spreadsheets: http://code.google.com/apis/spreadsheets/gadgets/

Answer 6

“Accessing Google Docs for JavaScript will be tedious to implement, and besides that, Google documentation is not that easy either. I have good sharing links that can help you access gs for js:

http://code.google.com/apis/documents/docs/3.0/developers_guide_protocol.html#UploadingDocs

http://code.google.com/apis/spreadsheets/gadgets/

http://code.google.com/apis/gdata/docs/js.html

http://www.mail-archive.com/[e-mail]/msg01924.html

Maybe this will help you ...

Answer 7

For this type you should use Google Fusion Tables . The API is designed for this purpose.

Answer 8

Sorry, this is a disgusting answer. Apparently, this has been a question for almost two years, so don't hold your breath.

Here is the official request you can "star"

Probably the closest you can come to is shuffling its own service using Google App Engine / Python and revealing everything you need with your own JS library, Although I would like for me to have a better solution.

Answer 9

In this fast-paced world, most of these links are out of date.

Now you can use the Google Drive web interfaces :

• Java
• Php
• Javacript
• . NET
• Python
• Ruby
• and others...

Answer 10

you can do this using Sheetsee.js and tabletop.js

• git example
• one more example