JavaScript asynchronous function Google Sheet API, data availability is delayed due to being a formula retrieval? How to overcome?

Question

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.

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