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

Sunday, 23/08/2020

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:

Read / write cell values (via spreadsheets.values https://developers.google.com/sheets/reference/rest/v4/spreadsheets.values )
Read / write any aspect of the spreadsheet (via the spreadsheets https://developers.google.com/sheets/reference/rest/v4/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:

enable the Sheets API at https://console.developers.google.com/apis/library , search for the keyword “sheets” , select “Google Sheets API” and click enable
Go to https://console.developers.google.com/projectcreate to create a new project
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:

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 .
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

Create button: <button onclick="handleSignInClick()">Đồng bộ</button>
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>
You need to initialize the initClient () function to authenticate the user before working with Google Sheets

function initClient() {
      var API_KEY = 'YOUR-API-KEY';
      var CLIENT_ID = 'YOUR-CLIENT-ID';
      var SCOPE = 'https://www.googleapis.com/auth/spreadsheets';

      gapi.client.init({
        'apiKey': API_KEY,
        'clientId': CLIENT_ID,
        'scope': SCOPE,
        'discoveryDocs': ['https://sheets.googleapis.com/$discovery/rest?version=v4'],
      }).then(function() {
        gapi.auth2.getAuthInstance().isSignedIn.listen(updateSignInStatus);
        updateSignInStatus(gapi.auth2.getAuthInstance().isSignedIn.get());
      });
    }

    function handleClientLoad() {
      gapi.load('client:auth2', initClient);
    }

    function updateSignInStatus(isSignedIn) {
      if (isSignedIn) {
        createSheet();
      }
    }

    function handleSignInClick(event) {
      gapi.auth2.getAuthInstance().signIn();
    }

function initClient() {

var API_KEY = 'YOUR-API-KEY';

var CLIENT_ID = 'YOUR-CLIENT-ID';

var SCOPE = 'https://www.googleapis.com/auth/spreadsheets';

gapi.client.init({

'apiKey': API_KEY,

'clientId': CLIENT_ID,

'scope': SCOPE,

'discoveryDocs': ['https://sheets.googleapis.com/$discovery/rest?version=v4'],

}).then(function() {

gapi.auth2.getAuthInstance().isSignedIn.listen(updateSignInStatus);

updateSignInStatus(gapi.auth2.getAuthInstance().isSignedIn.get());

});

}

function handleClientLoad() {

gapi.load('client:auth2', initClient);

}

function updateSignInStatus(isSignedIn) {

if (isSignedIn) {

createSheet();

}

function handleSignInClick(event) {

gapi.auth2.getAuthInstance().signIn();

}

After authenticating, we will create the SpreadSheets:

function createSheet() {
      var spreadsheetBody = {
        "properties": {
            "title": "YOUR-SHEET-NAME",
        },
      };

      var request = gapi.client.sheets.spreadsheets.create({}, spreadsheetBody);
      request.then(function(response) {
        console.log(response.result);
      }, function(reason) {
        console.error('error: ' + reason.result.error.message);
      });
    }

function createSheet() {

var spreadsheetBody = {

"properties": {

"title": "YOUR-SHEET-NAME",

};

var request = gapi.client.sheets.spreadsheets.create({}, spreadsheetBody);

request.then(function(response) {

console.log(response.result);

}, function(reason) {

console.error('error: ' + reason.result.error.message);

});

}

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

function syncToSheet() {
      var params = {
        spreadsheetId: 'YOUR_SPREADSHEET_ID', //bạn lấy từ response.result lúc createSheet thành công
        range: 'YOUR_SHEET_NAME',  //chính là title bạn khai báo lúc tạo SpreadSheet
        valueInputOption: 'RAW', 
      };

      var valueRangeBody = { //data bạn cần đồng bộ
         "values": [
            ['STT', 'Name', 'Age'],
            ['1', 'Nguyễn A', 12],
            ['2', 'Phan B', 13]
        ]
      };

      var request = gapi.client.sheets.spreadsheets.values.update(params, valueRangeBody);
      request.then(function(response) {
        console.log(response.result);
      }, function(reason) {
        console.error('error: ' + reason.result.error.message);
      });
    }

function syncToSheet() {

var params = {

spreadsheetId: 'YOUR_SPREADSHEET_ID', //bạn lấy từ response.result lúc createSheet thành công

range: 'YOUR_SHEET_NAME', //chính là title bạn khai báo lúc tạo SpreadSheet

valueInputOption: 'RAW',

};

var valueRangeBody = { //data bạn cần đồng bộ

"values": [

['STT', 'Name', 'Age'],

['1', 'Nguyễn A', 12],

['2', 'Phan B', 13]

]

};

var request = gapi.client.sheets.spreadsheets.values.update(params, valueRangeBody);

request.then(function(response) {

console.log(response.result);

}, function(reason) {

console.error('error: ' + reason.result.error.message);

});

}

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

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

Introduce

Setting

Create and synchronize SpreadSheets

Create SpreadSheets

Synchronize with Sheet

UseState trong React Hook

Business logic vulnerabilities in website application security

Beginner's guide to R: Introduction

10 essential SublimeText plugins for JavaScript developers