I'm moving some python code to Node JS.
In python, I have connection to Google Sheet API v4 with a self.service = BuildService().build('sheets') function which I can initiate whenever I need connection to Google sheets.
I got problems when I move to Node JS because the example code on Google's quickstart is using a async function. I can get the example to work, but I can't use it the same way I have it in Python, in that I can't get the value out of the function. I have tried to use global variables inside the Node JS function to return to me the service build or even the result, but when I make a request to it, it always gives me a promise. I can use await but I always have to wrap it inside a function and it seems like the result can't come out of the function because it is async.
Here is what the code flow looks like in python and in Node
Python:
service = BuildService().build('sheets')
key = 'xxxxxxxx'
ranges = ['test!A1']
res = service.spreadsheets().values().get(spreadsheetId=key, range=ranges).execute()
in Node JS
var sheets = authorize() // --> sheets {context: {…}, spreadsheets: Resource$Spreadsheets}
var id = 'xxxxxxx'
var ranges = ['test!A1']
const res = sheets.spreadsheets.values.batchGet({
spreadsheetId: id,
ranges: ranges_ar, valueRenderOption: "FORMATTED_VALUE"}
)
// res --> Promise
// Promise {[[PromiseState]]: 'pending', [[PromiseResult]]: undefined, Symbol(async_id_symbol): 16, Symbol(trigger_async_id_symbol): 1}
I use authorize().then(listMajors).catch(console.error); like in the quickstart example to get it work, but can't get the result to come out of the function. Wrapping it does no good, because it then can't come out of the wrapper.
Is there a way to make a connection to Google API V4 without using async? I want to rewrite my python code in Node JS using the same flow. Is this possible? Or does going over to Node JS means I have rearchitect all of my code?
full script, by Tanaike's suggestion, not working yet:
// https://developers.google.com/sheets/api/quickstart/nodejs
const fs = require('fs');
const path = require('path');
const process = require('process');
const {authenticate} = require('@google-cloud/local-auth');
const {google} = require('googleapis');
const request = require("sync-request");
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
const CREDENTIALS_PATH = 'xxxxxx'
const TOKEN_PATH = 'xxxxxxxx';
function loadSavedCredentialsIfExist() {
try {
const content = fs.readFile(TOKEN_PATH);
const credentials = JSON.parse(content);
return google.auth.fromJSON(credentials);
} catch (err) {
return null;
}
}
function saveCredentials(client) {
const content = fs.readFile(CREDENTIALS_PATH);
const keys = JSON.parse(content);
const key = keys.installed || keys.web;
const payload = JSON.stringify({
type: 'authorized_user',
client_id: key.client_id,
client_secret: key.client_secret,
refresh_token: client.credentials.refresh_token,
});
fs.writeFile(TOKEN_PATH, payload);
}
function authorize() {
let client = loadSavedCredentialsIfExist();
if (client) {
} else{
client = authenticate({
scopes: SCOPES,
keyfilePath: CREDENTIALS_PATH,
});
if (client.credentials) {
saveCredentials(client);
}
}
const sheets = google.sheets({version: 'v4', client});
return sheets
}
var sheets = authorize()
var id = 'xxxxxxx'
var ranges= ['Data!A2:E10']
const accessToken = sheets.context._options.auth.credentials.access_token;
const query = new URLSearchParams({ ranges, valueRenderOption: "FORMATTED_VALUE" }).toString();
const url = `https://sheets.googleapis.com/v4/spreadsheets/${id}/values:batchGet?${query}`;
const resObj = request("GET", url, { headers: { Authorization: `Bearer ${accessToken}` }});
const res = resObj.getBody("utf8");
console.log(res);
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?
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);
}
sheets, id, and ranges are valid values, the values are retrieved from range of the Spreadsheet of id.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);
}