CODEWITHSUNDEEP
javascriptnode.jsgoogle-sheets-api
Matthew Wolman
Matthew Wolman

Reputation: 675

Google Sheets Node.js Quickstart Guide: How to return the rows array returned from API into a variable

I'm using Google's Sheets Quickstart API for Nodejs. I have everything installed properly and the code runs fine.

The main function is at the end of the code, called listMajors(). It produces a two-dimensional array of rows. The code simply maps the rows array into a console.log() including a couple of items from each row.

I'm planning to use this API in a complex app I'm creating. I can change the spreadsheet ID and range and get the values from my sheet fine.

What I want to do is get this 'rows' array outside of the function and into a new variable. I want to do this because a large part of my code is going to be written using the rows values and I don't want to have to write all the code inside the listMajors() function.

My question is, is there a way I can make this listMajors() function return the two-d array into a variable that I can use in the global scope? I haven't been able to accomplish this. I tried to just set the function equal to a variable and add a return rows; statement at the end of the function. I got the error "ReferenceError: listMajors is not defined".

Here is the original code:

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

// 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';

// 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);
});

/**
 * 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: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
    range: 'Class Data!A2:E',
  }, (err, res) => {
    if (err) return console.log('The API returned an error: ' + err);
    const rows = res.data.values;
    if (rows.length) {
      console.log('Name, Major:');
      // Print columns A and E, which correspond to indices 0 and 4.
      rows.map((row) => {
        console.log(`${row[0]}, ${row[4]}`);
      });
    } else {
      console.log('No data found.');
    }
  });
}

This was the change I was trying to make:

var data = function listMajors(auth) {
  const sheets = google.sheets({version: 'v4', auth});
  sheets.spreadsheets.values.get({
    spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
    range: 'Class Data!A2:E',
  }, (err, res) => {
    if (err) return console.log('The API returned an error: ' + err);
    const rows = res.data.values;
    if (rows.length) {
      //console.log('Name, Major:');
      // Print columns A and E, which correspond to indices 0 and 4.
      //rows.map((row) => {
      //  console.log(`${row[0]}, ${row[4]}`);
      //});
      return rows;
    } else {
      console.log('No data found.');
    }
  });
}

console.log(data);

I believe I could write all my other functions in a different file, import them, and then just call them inside the listMajors() function. This is a solution to my problem, but what I really want to know is if there is a way different way to accomplish this.

Upvotes: 2

Views: 2283

Answers (3)

linuxgenie
linuxgenie

Reputation: 75

You may look at my question ( I have added the workable solution in the end of the question)

I don't claim it to be the perfect solution as I m still working on understanding async, callback, promise in javascript however I have searched the internet and using row variable outside listmajor function was something I wanted to achieve and I did it with my code.

Nodejs And Google spreadsheet Integration ( Cannot read property 'length' of undefined )

Upvotes: 0

Jeff Rush
Jeff Rush

Reputation: 912

is there a way I can make this listMajors() function return the two-d array into a variable that I can use in the global scope?

Yes and you have to create a variable outside the function declaration and then assign the function output to created variable.

Here is a step by step guide how to create Array

  1. create majors[] array
  2. add majors[] array values from columns Name and Majors
  3. list majors[] values to see the whole data
  4. list majors[1] values to see the name and major (Anna, English)
// 1
let majors = [];

function listMajors(auth) {

  const sheets = google.sheets({version: 'v4', auth});

  sheets.spreadsheets.values.get({
    spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
    range: 'Class Data!A2:E',
  }, (err, res) => {

    if (err) return console.log('The API returned an error: ' + err);

    const rows = res.data.values;

    if (rows.length) {
// 2
      rows.map((row) => {
        majors.push(`${row[0]}, ${row[4]}`);
      });
    } else {
      console.log('No data found.');
    }
// 3
    console.log(majors);
    console.log('--------------------')
// 4
    console.log(majors[2])
  });
}

This is the console.log() output:

[
  'Alexandra, English', 'Andrew, Math',
  'Anna, English',      'Becky, Art',
  'Benjamin, English',  'Carl, Art',
  'Carrie, English',    'Dorothy, Math',
  'Dylan, Math',        'Edward, English',
  'Ellen, Physics',     'Fiona, Art',
  'John, Physics',      'Jonathan, Math',
  'Joseph, English',    'Josephine, Math',
  'Karen, English',     'Kevin, Physics',
  'Lisa, Art',          'Mary, Physics',
  'Maureen, Physics',   'Nick, Art',
  'Olivia, Physics',    'Pamela, Math',
  'Patrick, Art',       'Robert, English',
  'Sean, Physics',      'Stacy, Math',
  'Thomas, Art',        'Will, Math'
]
--------------------
Anna, English

From now you can use your majors[] array how ever you want.

If you want to have more control over your data, you can create major{} object

Almost same steps as above:

  1. create data{} object
  2. add to data{} object all values
  3. list data{} values to see the whole data
  4. create listData() function for easier row output
  5. list Anna in data{} which is located in row 4 in Google Sheets, but we avoid the first row, which means -1, so in data{} her index is 2.
// 1
let data = {
  names : [],
  gender : [],
  classLevel : [],
  homeState : [],
  major : [],
  extracurricularActivity : []
}

// 4
function listData(row){
    console.log(data.names[row]);
    console.log(data.gender[row]);
    console.log(data.classLevel[row]);
    console.log(data.homeState[row]);
    console.log(data.major[row]);
    console.log(data.extracurricularActivity[row]);
}

function listMajors(auth) {

  const sheets = google.sheets({version: 'v4', auth});

  sheets.spreadsheets.values.get({
    spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
    range: 'Class Data!A2:F',
  }, (err, res) => {

    if (err) return console.log('The API returned an error: ' + err);

    const rows = res.data.values;

    if (rows.length) {
// 2
      rows.map((row) => {
        data.names.push(row[0]);
        data.gender.push(row[1]);
        data.classLevel.push(row[2]);
        data.homeState.push(row[3]);
        data.major.push(row[4]);
        data.extracurricularActivity.push(row[5]);
      });
    } else {
      console.log('No data found.');
    }
// 3
    console.log(data);
    console.log('--------------------')
// 5
    listData(2);
  });
}

Here you can see an output for listData(2):

Anna
Female
1. Freshman
NC
English
Basketball

There are more advanced ways to do this, take a look at reference. I hope these examples will help you to start your Google Sheets API with Node.js

Reference:

Upvotes: 0

Tanaike
Tanaike

Reputation: 201388

  • You want to retrieve the values retrieved from sheets.spreadsheets.values.get() at other function.
  • You want to achieve this using googleapis with Node.js.
  • You have already been able to get and put values for Spreadsheet using Sheets API.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modified script:

In this case, as a test case, at first, main() is called. So at first, please modify as follows.

From:

authorize(JSON.parse(content), listMajors);

To:

authorize(JSON.parse(content), main);

And please add main() and modify listMajors() as follows.

main() and listMajors()

async function listMajors(auth) {
  const sheets = google.sheets({ version: "v4", auth });
  const res = await sheets.spreadsheets.values.get({
    spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
    range: 'Class Data!A2:E',
  });
  return res.data.values;
}

async function main(auth) {
  const data = await listMajors(auth);
  console.log(data);
}

By above modification, when main() is run, you can see the values retrieved with sheets.spreadsheets.values.get() at data.

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 2

Related Questions