CODEWITHSUNDEEP
javascriptnode.jsgoogle-sheets-api
dropTableUsers
dropTableUsers

Reputation: 365

How to return values read using Google Sheets API spreadsheets.values.get?

I'm currently working on a project in which I extract values from a Google Sheet. I am able to console.log the results which shows that I can access the values. However, when I try to return it to the larger node.js script for processing in other functions, the variable is still undefined when it should be an array of strings.

Here is the code I'm struggling with.

function extractValsTest(auth) {
const sheets = google.sheets({version: 'v4', auth});
var request = {
    spreadsheetId: //id,
    range: 'A1:B1'
}
var test = new Array();
test = sheets.spreadsheets.values.get(request, function(err, response){
    if (err) return err;
    test1 = String(response.data.values).split(',');
    console.log(test1);
    return test1;
});
console.log('Test = ' + test);}

Clearly, the value of test should be the value of test1, but instead it is undefined, while the console.log of test1 outputs the values that I want in test.

I've tried many different ways to get the values outside of the sheet function, such as returning, overriding a pre-existing variable in the function, and pushing the values to a pre-existing array.

I would be open to using another http client, but I can't work out the authorization required. Any links to Google projects done in other http clients would help as well.

Any help would be appreciated. Thanks in advance.

Upvotes: 4

Views: 10610

Answers (1)

dgopsq
dgopsq

Reputation: 144

Well, that is not the right way to use an asynchronous function. The function spreadsheets.values.get() does not return directly a value, but instead it calls a callback function with the returned values. So, you should do something like:

sheets.spreadsheets.values.get(request, function(err, response) {
  var test = String(response.data.values).split(',');
  console.log(test);

  // Work with the "response" variable inside this callback
}

Since it's an asynchronous function, if you print outside something that you compute inside the callback it won't work, because the console.log(test) outside won't wait for spreadsheets.values.get() to complete and return the results. So you have to add everything that is related with the returned value of spreadsheets.values.get() inside the callback.

Upvotes: 3

Related Questions