Google Sheet integration in Express JS

Question

How can I create a feature in Express JS where users can authorize their Google account with our app. So, we can fetch the google sheet from their accounts and show it in my express app, and if users do something on the spreadsheet on my app, it will save automatically and reflect that on the google sheet.

Also, is there any tutorial and documentation to do that?

Answer 1

Prerequisite:- Creating access credentials

1. Visit the console.cloud.google.com.
2. Create a new project if you do not have an existing project.
3. Select the project and select APIs and services.
4. Click on ENABLE APIS AND SERVICES.
5. In the search box, search for google sheets API then enable it for the project created.
6. Once you have enabled the API, select CREATE CREDENTIALS to access the API. In the drop-down menu, select Service Account.
7. In the next screen, provide the details required then click CREATE.
8. The next two steps are optional, just click CONTINUE and then DONE.
9. Copy the email address and save it on the clipboard. Click on the email address to go to the next screen. Under keys, select Create new key.
10. Select JSON as the key type then Create. This process downloads a JSON file of the Key.
11. Move the downloaded JSON file into the same folder as the project and, since the name is too long, we can rename it as keys.json.

The file Looks like

{
  "type": "service_account",
  "project_id": "YOUR PROJECT ID",
  "private_key_id": "YOUR PRIVATE KEY ID",
  "private_key": "YOUR PRIVATE KEY ",
  "client_email": "YOUR CLIENT EMAIL",
  "client_id": "YOUR CLIENT ID",
  "auth_uri": "YOUR AUTH URI",
  "token_uri": "YOUR TOKEN URI",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/..."
}

1. Install googleapis

npm install –save googleapis

2. Require in the root file

const { google } = require("googleapis");

3. Creating the auth object

 const auth = new google.auth.GoogleAuth({
        keyFile: "keys.json", //the key file
        //url to spreadsheets API
        scopes: "https://www.googleapis.com/auth/spreadsheets", 
    });

4. Client instance of the auth object

    //Auth client Object
    const authClientObject = await auth.getClient();

5. The instance of Google sheets API

//Google sheets instance
    const googleSheetsInstance = google.sheets({ version: "v4", auth: authClientObject });

6. Extract the spreadsheets ID from the URL

The spreadsheet ID to be obtained from the URL of the Google sheets. It is the alphanumeric value that is between the /d/ and the /edit in the URL of your spreadsheet.

// spreadsheet id
const spreadsheetId = "YOUR SPREADSHEET ID";

7. Writing into the spreadsheet

Writing into a spreadsheet via frontend

The function to write into the spreadsheets takes the auth object, spreadsheet ID, and the range of cells to write onto, the value entered by the user, and the resource object containing the information to insert into the rows.

The resource object has a child value, which is an array of the data to be entered into the sheets. The array length depends on the number of columns of the spreadsheet.

//write data into the google sheets
await googleSheetsInstance.spreadsheets.values.append({
    auth, //auth object
    spreadsheetId, //spreadsheet id
    range: "Sheet1!A:B", //sheet name and range of cells
    valueInputOption: "USER_ENTERED", // The information will be passed according to what the usere passes in as date, number or text
    resource: {
        values: [["Git followers tutorial", "Mia Roberts"]],
    },
});