Search code examples
node.jsgoogle-apps-scriptgoogle-sheetsgoogle-sheets-apigoogle-api-nodejs-client

How to wait for google spreadsheet call and set it inside of a for loop

To get row from a google spreadsheet this code will work:

this.sheetsService.spreadsheets.values.get({
  spreadsheetId,
  range,
}, (err, result) => {
  if (err) {
    // Handle error
    console.log(err);
  } else {
    const numRows = result.values ? result.values.length : 0;
    console.log(`${numRows} rows retrieved.`);
    //Here I can use my rows
  }
});

but how can I wait for this function to finish? Example:

console.log("1");
this.sheetsService.spreadsheets.values.get({
  spreadsheetId,
  range,
}, (err, result) => {
  if (err) {
    // Handle error
    console.log(err);
  } else {
    console.log("2");
  }
});
console.log("3");

This will most likely print 132. Also is there a way to get the result and err outside of the current scope?

pseudo code from what I possible mean (should print 123):

console.log("1"); //Print 1
var getRows = this.sheetsService.spreadsheets.values.get({
  spreadsheetId,
  range,
}
console.log("2"); //Print 2

if (getRows.err) {
    // Handle error
    console.log(getRows.err);
} else {
    const numRows = getRows.result.values ? getRows.result.values.length : 0;
    console.log(`${numRows} rows retrieved.`);
    console.log("3") //Print 3
}

Edit: I want the err outside of the scope so I can do somethng like this: (If the sheet call fails with code 429, the programm will sleep for 100sek and the try it again) pseudo code:

for(var i = 1; i < 5; i++){
var getRows = this.sheetsService.spreadsheets.values.get({
  spreadsheetId,
  range,
}
console.log("2"); //Print 2

if (getRows.err) {
    // Handle error
    if(getRows.err.code === 429){
        console.log("Too many requests, try again in 100sek");
        await sleep(100000); //sleep function with 100sek
        continue
    }
    //Some other code here
} else {
    //logic for the results
    console.log("3") //Print 3
}
console.log("Finished");
}

So 123 will be correctly printed and if the call fails it will be retried after 100sek with maximal 5 tries.

Solution

  • You can use promises instead of callbacks or async/await:

    Snippet:

    console.log("1");
var i = 0;
function loop(){
if( ++i > 5 ) return; 
this.sheetsService.spreadsheets.values.get({
  spreadsheetId,
  range,
})
.then(result => {
    console.log(result);
    console.log("2");
})
.then(()=>console.log("3"))
.catch(err=>{
  console.log(err);
  if(err.code === 429){
     sleep(100000).then(loop) 
  }
})
}

    References: