google sheet api v4 non async function service build with Node js

In your situation, I thought that when sync-request is used, your goal can be achieved. But, the repository of sync-request says as follows.

N.B. You should not be using this in a production application. In a node.js application you will find that you are completely unable to scale your server. In a client application you will find that sync-request causes the app to hang/freeze. Synchronous web requests are the number one cause of browser crashes. For production apps, you should use then-request, which is exactly the same except that it is asynchronous.

Please be careful about this. From this situation, I said that in the current stage, I think that using async/await might be better. But, from your following reply,

I think this is great, i'm ok with it hanging. I'm not deploying it on any browser. How do I use this to build a Google Sheet API connector?

From this reply, if you want to achieve your goal using sync-request, how about the following sample script?

Sample script:

Before you use this script, please install "sync-request" with npm install sync-request.

From your reply and your updated question, I added a script for retrieving the access token.

const fs = require("fs");
const readline = require("readline");
const { google } = require("googleapis");
const request = require("sync-request");

const CREDENTIALS_PATH = "###"; // Please set your credential file with the path.
const TOKEN_PATH = "token_file.json"; // Please set your token file with the path.

const SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]; // This scope is from your script.

function getAccessToken() {
  if (!fs.existsSync(CREDENTIALS_PATH)) {
    throw new Error("No credential file.");
  }
  const creds = fs.readFileSync(CREDENTIALS_PATH, "utf-8");
  const credentials = JSON.parse(creds);
  const { client_secret, client_id, redirect_uris } = credentials.installed;
  const oAuth2Client = new google.auth.OAuth2(
    client_id,
    client_secret,
    redirect_uris[0]
  );
  if (fs.existsSync(TOKEN_PATH)) {
    const tokenStr = fs.readFileSync(TOKEN_PATH, "utf-8");
    const token = JSON.parse(tokenStr);
    oAuth2Client.setCredentials(token);
  } else {
    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. And, please run script again: ",
      (code) => {
        rl.close();
        oAuth2Client.getToken(code, (err, token) => {
          if (err) throw new Error(err);
          oAuth2Client.setCredentials(token);
          fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
            if (err) throw new Error(err);
            console.log("Token stored to", TOKEN_PATH);
          });
        });
      }
    );
  }
  if (!oAuth2Client.credentials.access_token) return {};
  return { oAuth2Client, accessToken: oAuth2Client.credentials.access_token };
}

const { accessToken } = getAccessToken();
if (accessToken) {
  const spreadsheetId = "###"; // Please your Spreadsheet ID.
  const ranges = ["test!A1"]; // This is from your script.

  const query = new URLSearchParams({ ranges, valueRenderOption: "FORMATTED_VALUE" }).toString();
  const url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values:batchGet?${query}`;
  const resObj = request("GET", url, { headers: { Authorization: `Bearer ${accessToken}` } });
  const res = resObj.getBody("utf8");
  console.log(res);
}

• When this script is run, when your values of sheets, id, and ranges are valid values, the values are retrieved from range of the Spreadsheet of id.

Note:

• As additional information, if you want to use googleapis for Node.js, the following modification can be used.

  async function main() {
    const { oAuth2Client } = getAccessToken();
    const sheets = google.sheets({ version: "v4", auth: oAuth2Client });
    const res = await sheets.spreadsheets.values.batchGet({
      spreadsheetId: "### spreadsheet ID ###",
      ranges: ["### a1Notation ###"],
      valueRenderOption: "FORMATTED_VALUE",
    });
    console.log(res.data);
  }
  

References:

• sync-request
• Method: spreadsheets.values.batchGet