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

User · Answer

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  amp 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  amp includeGridData true                                                                axios get url     then function  response        console log response                                                                                                                                                               catch function  error        console log error

User · Answer

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 3 ways to do this today    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   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  amp  easily portable to JS     Migrating SQL data to a Sheet  code deep dive post  Formatting text using the Sheets API  code deep dive post  Generating slides from spreadsheet data  code deep dive post  Those and others in the Sheets API video library   The 3rd way to access Google APIs with JavaScript is from Node js apps using its client library  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  amp  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

User · Answer

For this type of thing you should use Google Fusion Tables  The API is designed for that purpose

User · Answer

You can read Google Sheets spreadsheets data in JavaScript by using the RGraph sheets connector   https   www rgraph net canvas docs import-data-from-google-sheets html  Initially  a few years ago  this relied on some RGraph functions to work its magic - but now it can work standalone  ie not requiring the RGraph common library    Some example code  this example makes an RGraph chart     lt  -- Include the sheets library -- gt   lt script src  RGraph common sheets js  gt  lt  script gt    lt  -- Include these two RGraph libraries to make the chart -- gt   lt script src  RGraph common key js  gt  lt  script gt   lt script src  RGraph bar js  gt  lt  script gt    lt script gt         Create a new RGraph Sheets object using the spreadsheet s key and        the callback function that creates the chart  The RGraph Sheets object is        passed to the callback function as an argument so it doesn t need to be        assigned to a variable when it s created     new RGraph Sheets  1ncvARBgXaDjzuca9i7Jyep6JTv9kms-bbIzyAxbaT0E   function  sheet                   Get the labels from the spreadsheet by retrieving part of the first row         var labels   sheet get  A2 A7                Use the column headers  ie the names  as the key         var key   sheet get  B1 E1                Get the data from the sheet as the data for the chart         var data                   sheet get  B2 E2       January             sheet get  B3 E3       February             sheet get  B4 E4       March             sheet get  B5 E5       April             sheet get  B6 E6       May             sheet get  B7 E7       June                        Create and configure the chart  using the information retrieved above            from the spreadsheet         var bar   new RGraph Bar               id   cvs               data  data              options                    backgroundGridVlines  false                  backgroundGridBorder  false                  xaxisLabels  labels                  xaxisLabelsOffsety  5                  colors     A8E6CF    DCEDC1    FFD3B6    FFAAA5                    shadow  false                  colorsStroke   rgba 0 0 0 0                    yaxis  false                  marginLeft  40                  marginBottom  35                  marginRight  40                  key  key                  keyBoxed  false                  keyPosition   margin                   keyTextSize  12                  textSize  12                  textAccessible  false                  axesColor    aaa                           wave             lt  script gt

User · Answer

JavaScript accessing Google Docs  would be tedious to implement and moreover Google documentation is also not that simple to get it   I have some good links to share by which you can achieve js access to gdoc      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 google-help-dataapi googlegroups com msg01924 html   May be these would help you out

User · Answer

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

User · Answer

In this fast changing world most of these link are obsolet   Now you can use Google Drive Web APIs    Java PHP Javacript  NET Python Ruby and others

User · Answer

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

User · Answer

you can do it by using Sheetsee js and tabletop js    example from git another Example

User · Answer

Sorry  this is a lousy answer   Apparently this has been an issue for almost two years so don t hold your breath    Here is the official request that you can  star   Probably the closest you can come is rolling your own service with Google App Engine Python and exposing whatever subset you need with your own JS library   Though I d love to have a better solution myself

User · Answer

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

User · Answer

edit  This was answered before the google doc s api was released  See Evan Plaice s answer and Dan Dascalescu s answer for more up-to-date   information    It looks lke you can  but it s a pain to use   It involves using 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 just about any Google Data API to access authenticated private feeds  This example uses the DocList API    and an example of writing a gadget that interfaces with spreadsheets  http   code google com apis spreadsheets gadgets

[javascript] How can I access Google Sheet spreadsheets only with Javascript?

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

Get sheet metadata

Get a range of cells

Examples related to javascript

Examples related to google-api

Examples related to google-sheets-api

Examples related to google-data-api

Examples related to google-api-js-client