7

I have a Google Sheet and I want to have its data updated from our server. Currently I'm using google sheet script editor. google sheet->tools->script editor https://developers.google.com/apps-script/guides/sheets

But I came to realize that there is another way to have my google sheet updated. And thats using google sheet api https://developers.google.com/sheets/api/samples/writing https://developers.google.com/sheets/api/reference/rest/ https://developers.google.com/sheets/api/samples/

So I would liked to know what are the differences. And find out what best suits for my case.

Mishel Parkour
  • 599
  • 5
  • 16
  • 2
    In most cases, Google-Apps-Script works faster for same tasks. Use API for special purposes only, when Apps-Script can't do smth API can. – Max Makhrov Jun 20 '18 at 08:33
  • 1
    Using Api, you can use the language of your choice-like python, java,C,nodejs,etc. – TheMaster Jun 20 '18 at 09:03
  • If you request reduced permissions from your users, you often need to use the REST API / client library instead of Apps Script native classes, due to the lack of scope awareness in them. So if you have only `drive.metadata.readonly` scope, `DriveApp` methods will generally throw an exception due to lack of permissions to access the underlying files (even if you just use metadata fields like name and mimetype in your code). – tehhowch Jun 20 '18 at 12:45
  • 1
    There are the situations that only Sheet API can do and SpreadsheetApp cannot do. For example, 3 situations of several sample situations are as follows. [Situation 1](https://stackoverflow.com/questions/50605735/delete-all-filer-criterias-but-preserve-filter/50614501#50614501), [Situation 2](https://stackoverflow.com/questions/50632352/renaming-sheets-with-full-sheet-charts-on-them-causes-spreadsheet-to-reload/50633895#50633895) and [Situation 3](https://stackoverflow.com/questions/50466080/placing-checkboxes-in-google-sheets-using-apps-script/50964935#50964935) – Tanaike Jun 22 '18 at 00:38

2 Answers2

11

Summary: If you are beginning to work with Sheets programmatically, use Google Apps Script.

Google Apps Script is an extension of (an old version of) JavaScript. It is executed on Google servers and has direct access to spreadsheets and other documents to which the owner account has access. It can also interact with the code on your company's server by handling GET and POST requests (doGet and doPost functions) and sending them (UrlFetchApp methods).

Google Sheets API is not tied to any language; it's just a collection of requests that can be sent to Google servers by whatever code in whatever language. So, the code on your company servers can send some GET or POST requests to Google following the API structure, in order to access or change the data in your sheets.

Google Apps Script can access Sheets API using an Advanced Service:

Much like Apps Script's built-in Sheets service, this API allows scripts to read, edit, format and present data in Google Sheets. In most cases, the built-in service is easier to use, but this advanced service provides a few extra features.

  • One small correction. GAS Advance Services are just wrappers for their respective APIs and they do make HTTP requests that count towards UrlFetch quotas. – TheAddonDepot Jun 20 '18 at 16:58
  • I cannot find a function reference document for Google Apps Script. If it's true that there isn't one, and you don't know javascript, then maybe GAS is not so good for beginners. I've just spent 4 hours trying to write something that finds blank rows in a selection and deletes them, but without success. I'm an experienced programmer so I'm confident that with a function reference document I'd have cracked it quickly. – Aethelbald May 01 '19 at 13:57
  • 1
    There is a function reference document, you just weren't able to find it: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app – cryanbhu Aug 07 '20 at 07:46
0

Google Sheets Apps Script is the preferred method for data manipulation

Trajano Roberto
  • 179
  • 2
  • 7