Create SpreadSheet and synchronize data on Google Sheet with Google Sheet API

Tram Ho

Introduce

Google Sheets is a web-based spreadsheet application. It allows to store and organize information such as Microsoft Excel. While Google Sheets can’t offer all the advanced features like Excel, it’s pretty easy to create and edit spreadsheets from simple to complex.

The Google Sheets API allows us to edit and read any element of spreadsheet. Spreadsheets have a lot of beautification implementation and sheet functionality, so the API also has a lot of sitting there. The API provides two ways to interact with Spreadsheets:

You can view more information about collections and their usage through the following develop tutorials: https://developers.google.com/sheets/api/guides/concepts

Today we will learn how to create a linear google page and synchronize website data on it!

Setting

To be able to connect to the site with Google Sheets we need to create Key and Client_id to proceed with google account authentication. To do this we first need:

  1. enable the Sheets API at https://console.developers.google.com/apis/library , search for the keyword “sheets” , select “Google Sheets API” and click enable
  2. Go to https://console.developers.google.com/projectcreate to create a new project
  3. Go to https://console.developers.google.com/apis/credentials -> select your project and create API key and OAuth client id:

Edit API key is as follows:

  1. Go to OAuth consent screen -> select Application type public , enter Application name , select Support Email and enter Authorized domains as the root domain of your website (eg abc.com ) and Save again .
  2. Create OAuth client id respectively: Select Web Application -> fill in Name, Authorized JavaScript origins, Authorized redirect URIs (for Authorized JavaScript origins and Authorized redirect URIs fill in your website link, in case of test local, enter the link local eg http: // localhost: 8000 )

Create and synchronize SpreadSheets

Create SpreadSheets

First of all, in your html page add this line for me: <meta name="google-signin-client_id" content="your-client-id">

You can visit https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/create . Here we have all the sample code used for creating SpreadSheets. You just need to take it out and use it

  1. Create button: <button onclick="handleSignInClick()">Đồng bộ</button>
  2. Import additional library: <script async defer src="https://apis.google.com/js/api.js" onload="this.onload=function(){};handleClientLoad()" onreadystatechange="if (this.readyState === 'complete') this.onload()"> </script>
  3. You need to initialize the initClient () function to authenticate the user before working with Google Sheets

  1. After authenticating, we will create the SpreadSheets:

The returned result (response.result) will contain all information of the newly created SpreadSheet: spreadsheetId, spreadsheetUrl, ….

Synchronize with Sheet

Similar to the SpreadSheets creation, we just initialize the syncToSheet function and get the code from https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update

You just need to call syncToSheet again after the successful createSheet is done

This is the case of the script written in html file. If you want to separate the html and js files, you need to change the onclick event for the Sync button and replace the above imported script library with: <script src="https://apis.google.com/js/platform.js?onload=handleClientLoad"></script>

And this is the result:

Good luck !

Reference source: https://developers.google.com/sheets/api/reference/rest/

Share the news now

Source : Viblo