Instructions for creating API write data on google sheet

In the project, I have recently been assigned a task related to saving data on google sheet after customers merged pull request to perform calculations and reports to the superior or take examples with familiar friends. the use of google form but do not want to use the interface of google but want to create a separate html form as you want and then save the feedback on google sheet, the google sheet side does not have api available for me to use so we will have to manually build api to write data. In this article I will share to everyone a simple way to build api push data from some source into google sheet.

Set up google sheet

First we need to create a google sheet, if you have it, you can skip this step, access the link: https://docs.google.com/spreadsheets/ will open the google sheet interface as below. then select "Blank"

Set up google script

Once you have a google sheet page, we will write scripts to write data, on the toolbar select Tools -> Script editor

The browser will open a google script tab

Replace the code in Code.gs to the following content

Note the variable SHEET_NAME is the name of the sheet on the google sheet page, the default when creating new will be "Sheet1"

Save the script, select File -> Save

Choose OK

The next step is to grant permission to the google script to use the account, select Run -> Run function -> setup

Select Review Permissions

The browser will open a new window, select the account you will grant it

Select Allow

After granting the account the next step, we will take the URL, select Publish -> Deploy as web app

The Who has access to the app section changes from "Only myself" to "Anyone, even anonymous" and then select Deploy

Copy the URL and note it to use for push data

Map the google script via google sheet

After writing the script, we will return to the google sheet to edit as follows to check whether the data writing has worked

• In row 1, add 2 values: field_1 and field_2

• Any value set is fine, but follow the naming rules of the programming languages, so does the order in any order, as long as in row 1.

  

• Copy the URL (created in google script) and add a paragraph as below and then throw it into the browser and press Enter to test

  ?field_1=123&field_2=456

  1 2	?field_1=123&field_2=456

• In the browser screen shown as below has added data successfully, row is the number of lines added to the google sheet

• Return to google sheet to check the results

  

After successfully checking the data recording, all people need to do is build the data processing function in their application and then call the URL (created in google script) with GET method and pass parameters into stars. for corresponding value set in line 1 in google sheet is ok. Below is an example in the javascript language (using the jQuery library), other languages ​​similar to people google further offline.

Reference source

• https://medium.com/@dmccoy/how-to-submit-an-html-form-to-google-sheets-without-google-forms-b833952cc175
• https://gist.github.com/willpatera/ee41ae374d3c9839c2d6