CODEWITHSUNDEEP
javascriptnode.jsgoogle-sheetsasync-awaitgoogle-sheets-api
goryef
goryef

Reputation: 1489

nodejs reading value in google spreadsheet with await/async

I am using for-loop to iterate thru sheets in a google spreadsheet and access specific cell within each sheet. I have a google spreadsheet with five sheets Sheet1, Sheet2,.. , Sheet5. The value if the cell that I am reading is Value1,..., Value5. I hope to get results displayed as follow:

Sheet1
Value1
Sheet2
Value2
:
Sheet5
Value5

However I get:

Sheet1
Undefined
Sheet2
Undefined
:
Sheet5
Undefined
Value1
Value2
:
Value5.

I realize that I need to use async/await, but can't figure the right away with google.spreadsheet.get. Thanks in advance for any guidance. My code:

function getData (auth) {
  var sheets = google.sheets('v4')
  sheets.spreadsheets.get(
    {
      auth: auth,
      spreadsheetId: sheet_id
    },
    (err, response) => {
      if (err) console.log('The API returned an error: ' + err)
    }
  )
  var no_sheets = response.data.sheets.length

  for (var i = 0; i < no_sheets; i++) {
    try {
      console.log(response.data.sheets[i].properties.title)
      var sheet_title = response.data.sheets[i].properties.title
      var cell_value = getCellValue(auth, sheet_title)
      console.log(cell_value)
    } catch (err) {
      console.log(err)
      continue
    }
  }
}

function getCellValue (auth, sheet_title) {
  sheets.spreadsheets.values.get(
    {
      auth: auth,
      spreadsheetId: sheet_id,
      range: sheet_title + '!A1:A1'
    },
    (err, response) => {
      if (err) {
        console.log('The API returned an error: ' + err)
        return
      }
      var rows = response.data.values
      if (rows.length === 0) {
        console.log('No data found.')
      } else {
        for (var i = 0; i < rows.length; i++) {
          var cell_value = rows[i]
        }
        console.log(cell_value)
        return cell_value
      }
    }
  )
}

Upvotes: 1

Views: 2493

Answers (1)

Bj&#246;rn Hei&#223;
Bj&#246;rn Hei&#223;

Reputation: 1728

If you want to use async/await instead of callbacks you can, because the googleapi returns a Promise. Those Promises are the foundation of async/await and were the goto before. If you use async/await or Promises your code will be much easier to read and understand.

So instead of calling:

sheets.spreadsheets.get({
  auth: auth,
  spreadsheetId: sheet_id
},
(err, response) => {
  if (err) console.log('The API returned an error: ' + err)
});

you can call:

try {
  const response = sheets.spreadsheets.get({
    auth: auth,
    spreadsheetId: sheet_id
  });
}
catch(err) {
  console.log('The API returned an error: ' + err);
}

Thing is that you can use the await keyword only in async functions. You can read more about async/await here.

Upvotes: 3

Related Questions