1

I'm writing a formula using Google Sheets gapi.update, and then immediately calling a function makeApiCall, attempting to retrieve the data from that cell, with gapi.get.

The first iteration of this retrieval query almost inevitably returns undefined, as the formula has not processed in time for the request.

I have tried making a for loop to repeat the request, in comparison to !==null, this loop executes the 4 iterations prior to retrieving a single response. However, the desired value is usually returned to the console after the second query. I also attempted to rewrite this with time delays, which don't appear to actually sleep, they simply delay displaying the data. I also attempted to rewrite with async function + await + return and managed to await the return of undefined but not loop for the required data.

Is there a simple way to either delay the requests. Or awit between each?

async function makeApiCall(pos) {
  var params = {
    spreadsheetId: 'GoogleSpreadsheetID',
    range: 'Sheet1!B1',
  };

  var i;
  for (i = 0; i < 4; i++) {
    var request = gapi.client.sheets.spreadsheets.values.get(params);
    request.then(function(response) {
      dataOu = (response.result.values);
      if (dataOu.value !== null) {
        displayRes(pos)
      }
    });

  }

My expected result is that my global variable dataOu retrieves >null from the formula and if not it should repeat the query. Is there a way to write this as a proper asynchronous function in which the request is repeated if the desired response is not received. or a way of instituting a timer between for loop iterations. This is not a simple asynchronous call as it is not just waiting for a response it is wanting to retrieve a particular response.

adiga
  • 34,372
  • 9
  • 61
  • 83
psquizzle
  • 175
  • 3
  • 15
  • possible duplicate of [How do I return the response from an asynchronous call?](https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call) – AZ_ Jul 31 '19 at 07:06
  • Possible duplicate of [How do I return the response from an asynchronous call?](https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call) – Aaron_ab Jul 31 '19 at 07:09

1 Answers1

1

Yes, this should be easily possible with await:

async function makeApiCall(pos) {
    const params = {
        spreadsheetId: 'GoogleSpreadsheetID',  
        range: 'Sheet1!B1',  
    };

    let response = await gapi.client.sheets.spreadsheets.values.get(params);
    while (response.result.values == null) { // or whatever you need to check
        await new Promise(resolve => setTimeout(resolve, 5)); // delay the loop a bit
        response = await gapi.client.sheets.spreadsheets.values.get(params); // try again
    }
    const dataOu = (response.result.values);
    if (dataOu.value !== null) {
        displayRes(pos);
    }
}
Bergi
  • 630,263
  • 148
  • 957
  • 1,375
  • This is the perfect solution, I clearly need some more practice on writing Promises. Only thing is this code contains a for loop from the original and your while loop, only the while loop is necessary. – psquizzle Jul 31 '19 at 23:14
  • @psquizzle Ah ok, I was somehow under the impression that you wanted to do 4 requests. – Bergi Aug 01 '19 at 19:00