HTTP Post response in Typescript to update google spreadsheet

Question

I am updating spreadsheets from google API at the following url: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append

In the update form I am filling in the following properties: in the spreadsheetId field I put the ID of the spreadsheet and in the range field the name of the sheet, in the insertDataOption field I choose INSERT_ROWS, in valueInputOption, RAW and in the Request body field the following object:

{
  "values": [
    [
      "id",
      "ser",
      "IP",
      "host",
      "type",
      "auth"
    ],
    [
      "1",
      null,
      null,
      "",
      "Web",
      ""
    ],
    [
      "2",
      null,
      "191.174.230.02",
      "",
      "Proxy",
      ""
    ]
  ]
}

The HTTP Post response is as follows: POST https://sheets.googleapis.com/v4/spreadsheets/[spreadsheetId-lex.europa.eu/values/[range-lex.europa.eu:append

My problem: how can I form the HTTP Post response with the properties and the object that I want to add? That is, how do I tell the response what data I want to add in the spreadsheet?

Answer 1

How to append data to a Spreadsheet with Node

Note: This does not use Typescript, but since plain JavaScript is also valid Typescript, this should work for your Typescript project.

First steps

Follow the Node.js Quickstart. This will get your project set up with:

• GCP project
• API activation
• OAuth consent screen
• Credentials - be sure to download this and save as credentials.json in the project folder.

It will also get you to install the Node.js library:

npm install googleapis

Making a request

Below is code that is adapted from the quickstart to use an append request:

const fs = require('fs');
const readline = require('readline');
const {google} = require('googleapis');

// =============
// AUTHORIZATION
// =============

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
const TOKEN_PATH = 'token.json';

fs.readFile('credentials.json', (err, content) => {
  if (err) return console.log('Error loading client secret file:', err);
  authorize(JSON.parse(content), append);
});

function authorize(credentials, callback) {
  const {client_secret, client_id, redirect_uris} = credentials.installed;
  const oAuth2Client = new google.auth.OAuth2(
      client_id, client_secret, redirect_uris[0]);

  fs.readFile(TOKEN_PATH, (err, token) => {
    if (err) return getNewToken(oAuth2Client, callback);
    oAuth2Client.setCredentials(JSON.parse(token));
    callback(oAuth2Client);
  });
}

function getNewToken(oAuth2Client, callback) {
  const authUrl = oAuth2Client.generateAuthUrl({
    access_type: 'offline',
    scope: SCOPES,
  });
  console.log('Authorize this app by visiting this url:', authUrl);
  const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout,
  });
  rl.question('Enter the code from that page here: ', (code) => {
    rl.close();
    oAuth2Client.getToken(code, (err, token) => {
      if (err) return console.error('Error while trying to retrieve access token', err);
      oAuth2Client.setCredentials(token);
      fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
        if (err) return console.error(err);
        console.log('Token stored to', TOKEN_PATH);
      });
      callback(oAuth2Client);
    });
  });
}

// =============
//    APPEND
// =============

function append(auth){
    const sheets = google.sheets({version: 'v4', auth});
    sheets.spreadsheets.values.append({
        spreadsheetId: '13rdolwpUD4h4RTuExxxxxx', 
        range: 'Sheet1!A1', 
        valueInputOption: 'RAW',
        insertDataOption: 'INSERT_ROWS',  
        resource: {
            "values": [
                [
                  "id",
                  "ser",
                  "IP",
                  "host",
                  "type",
                  "auth"
                ],
                [
                  "1",
                  null,
                  null,
                  "",
                  "Web",
                  ""
                ],
                [
                  "2",
                  null,
                  "191.174.230.02",
                  "",
                  "Proxy",
                  ""
                ]
              ]
        },
    })
}

Be sure to replace the spreadsheet ID with your one.

When you run this for the first time with node . then you should be prompted to go to a URL to get the auth code for the OAuth flow. Once you are authorized, then the script should execute.

Reference

• Quickstart
• Append examples