I have started using nodejs ( and have a beginner level understanding of javascript ) .
Currently following google Spreadsheet API guide to building a small application. https://developers.google.com/sheets/api/quickstart/nodejs
While I was able to establish the connectivity with my google sheet and able to fetch the data I want to achieve it in a specific way as below.
Put the data fetching code in one file ( fetchdatafromgooglesheet.js getdata() function to fetch the data and return it to main function as rows ) and later use the data in my main file ( start.js ) and then perform the actions based on the values.
However I m not able to achieve this sharing the code of both files. Please help .
start.js
const googlesheet = require('./fetchdatafromgooglesheet');
var rows=googlesheet.getData();
console.log(rows.length);
fetchdatafromgooglesheet
const fs = require('fs');
const readline = require('readline');
const {google} = require('googleapis');
//var rows=null;
// If modifying these scopes, delete token.json.
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
// The file token.json stores the user's access and refresh tokens, and is
// created automatically when the authorization flow completes for the first
// time.
const TOKEN_PATH = 'token.json';
var rows;
const getData= ()=>
{
// Load client secrets from a local file.
fs.readFile('credentials.json', (err, content) => {
if (err) return console.log('Error loading client secret file:', err);
// Authorize a client with credentials, then call the Google Sheets API.
authorize(JSON.parse(content), listMajors);
});
return rows;
}
/**
* Create an OAuth2 client with the given credentials, and then execute the
* given callback function.
* @param {Object} credentials The authorization client credentials.
* @param {function} callback The callback to call with the authorized client.
*/
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]);
// Check if we have previously stored a token.
fs.readFile(TOKEN_PATH, (err, token) => {
if (err) return getNewToken(oAuth2Client, callback);
oAuth2Client.setCredentials(JSON.parse(token));
callback(oAuth2Client);
});
}
/**
* Get and store new token after prompting for user authorization, and then
* execute the given callback with the authorized OAuth2 client.
* @param {google.auth.OAuth2} oAuth2Client The OAuth2 client to get token for.
* @param {getEventsCallback} callback The callback for the authorized client.
*/
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);
// Store the token to disk for later program executions
fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
if (err) return console.error(err);
console.log('Token stored to', TOKEN_PATH);
});
callback(oAuth2Client);
});
});
}
/**
* Prints the names and majors of students in a sample spreadsheet:
* @see https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
* @param {google.auth.OAuth2} auth The authenticated Google OAuth client.
*/
function listMajors(auth) {
const sheets = google.sheets({version: 'v4', auth});
sheets.spreadsheets.values.get({
spreadsheetId: '1MMx_OcwmQApgKJUVdU0ySC_lUx1FB6-81AfV3E',
range: 'Sheet2!A2:F',
}, (err, res) => {
if (err) return console.log('The API returned an error: ' + err);
var fetchedData = res.data.values;
rows=fetchedData;
});
}
module.exports={getData};
Getting below error while execution.
D:\study\nodejs\googlesheet\start.js:5
console.log(rows.length);
^
TypeError: Cannot read property 'length' of undefined
at Object.<anonymous> (D:\study\nodejs\googlesheet\start.js:5:18)
at Module._compile (internal/modules/cjs/loader.js:1138:30)
at Object.Module._extensions..js (internal/modules/cjs/loader.js:1158:10)
at Module.load (internal/modules/cjs/loader.js:986:32)
at Function.Module._load (internal/modules/cjs/loader.js:879:14)
at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:71:12)
at internal/main/run_main_module.js:17:47
After some research and following up with suggestions given by others I have modified the code as below it gets the job done but I think its not the right way of doing things. Please let me know (here is the modified code )
function fetchData () {
var rows=googlesheet.getData();
if (rows==undefined && rows==null) {
setTimeout(fetchData, 300);
} else {
//setTimeout(fetchData, 300); // try again in 300 milliseconds
console.log(rows.length);
}
}
fetchData();
