Using promises for mulitple queries to Google sheets

Question

I'm a beginner coder, and would like some help with speeding up my Google sheet queries. I have looked around a lot and cannot find a solution. I'm trying to run 25 queries to a Google sheet quickly, and this is how I'm trying it:

Run a query to a Google sheet.
Push the result to an array.
Run a different query.
Push the result to the same array.
Run the next query... x 25.
Draw a table of the final result.

The problem is that if I run the quires one by one they take 25 seconds to complete. If I run the quires in a for loop the results come back in 2 second, but asynchronously, and the data is all out of wack. Perhaps, I should use a promise, or something else. Please can you help.

Here is the code with the slow version:

   var resultData;
var allData = [];
var i = 0;

function startQuery() {
  i = 0;
  allData.length = 0;
  runOnecode();
}

function runOnecode() {
  var str  = "";
  str += $(".google-visualization-controls-rangefilter-thumblabel").text();
  var until = str.substring(10);
  var from = str.substring(0,10);
  var center = $("#centerSelect option:selected").text();
  var query = [
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND G =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND H =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND I =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND J =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND K =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND N =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND O =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND P =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND Q =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND R =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND S =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND T =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND U =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND V =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND W =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND X =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND Y =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND Z =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND AA =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND AB =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND AC =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND AD =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND AE =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND AF =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND AG =\"No\" ")
  ];

  getData(query[i]);
}
</script>
<script>
function getData(incomingQuery) {

  // Prepare the query 
  var queryString = encodeURIComponent(incomingQuery);
  var query = new google.visualization.Query(
    //Collect the data from this spreadsheet
  'MySpreadSheet URL' + queryString);
  query.send(handleSampleDataQueryResponse);

  //Handle any errors from the Google server
  function handleSampleDataQueryResponse(response) {
    if (response.isError()) {
      alert('Error in query: ' + response.getMessage() + ' ' +
        response.getDetailedMessage());
      return;
    }

    // get look into the resulting data and find the detail.     
    var datas = response.getDataTable();
    if (datas.Gf.length === 0) {
      resultData = 0;
    } else resultData = datas.Gf[0].c[0].v;

    allData.push(resultData);
    //     console.log(allData);

    if (i < 25) {
      runOnecode();

    } else {
      drawTable();
    }
    i++;
  }

}
</script>

Here is the fast version that returns the results asynchronously:

<script>
function runOnecode() {
  var str  = "";
  str += $(".google-visualization-controls-rangefilter-thumblabel").text();
  var until = str.substring(10);
  var from = str.substring(0,10);
  var center = $("#centerSelect option:selected").text();
  var query = [
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND G =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND H =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND I =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND J =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND K =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND N =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND O =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND P =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND Q =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND R =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND S =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND T =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND U =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND V =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND W =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND X =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND Y =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND Z =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND AA =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND AB =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND AC =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND AD =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND AE =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND AF =\"No\" "),
    ("SELECT COUNT(A) WHERE C =\"" + center + "\" AND todate(A)  >=date \'" + from + "\' AND todate(A)  <=date \'" + until + "\' AND AG =\"No\" ")
  ];
for(var i=0; i<25; i++){
  getData(query[i]);
// When all are done I will draw the table. 

}
}
</script>
<script>
function getData(incomingQuery) {

  // Prepare the query 
  var queryString = encodeURIComponent(incomingQuery);
  var query = new google.visualization.Query(
    //Collect the data from this spreadsheet
    'MySpreadSheet URL' + queryString);
  query.send(handleSampleDataQueryResponse);

  //Handle any errors from the Google server
  function handleSampleDataQueryResponse(response) {
  if (response.isError()) {
      alert('Error in query: ' + response.getMessage() + ' ' +
       response.getDetailedMessage());
      return;
    }

    // get look into the resulting data and find the detail.     
    var datas = response.getDataTable();
    console.log(datas);
    if (datas.Gf.length === 0) {
      resultData = 0;
    } else resultData = datas.Gf[0].c[0].v;

    allData.push(resultData);
      console.log(allData);
  }

}
</script>

score 2 · Answer 1 · answered Feb 09 '16 at 08:17

Using promises, you can do this

function runOnecode() {
    var str  = "";
    str += $(".google-visualization-controls-rangefilter-thumblabel").text();
    var until = str.substring(10);
    var from = str.substring(0,10);
    var center = $("#centerSelect option:selected").text();
    var query = [
        // your query data, removed for brevity ...
    ];
    Promise.all(query.map(getData))
    .then(function(results) {
        // results is an array of results in the same order as query
    })
    .catch(function(err) {
        // err is FIRST error - no other information about success/faill of other queries will be available
    });
}

function getData(incomingQuery) {
    return new Promise(function(fulfill, reject) {
        // Prepare the query 
        var queryString = encodeURIComponent(incomingQuery);
        var query = new google.visualization.Query(
            //Collect the data from this spreadsheet
            'MySpreadSheet URL' + queryString);
        query.send(handleSampleDataQueryResponse);

        //Handle any errors from the Google server
        function handleSampleDataQueryResponse(response) {
            if (response.isError()) {
                throw('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
            }

            // get look into the resulting data and find the detail.     
            var datas = response.getDataTable();
            console.log(datas);
            if (datas.Gf.length === 0) {
                resultData = 0;
            } else {
                resultData = datas.Gf[0].c[0].v;
            }
            fulfill(resultData);
        }
    });
}

if you need to get the state of all queries success/failure, you could use something like Q promises which has a allSettled method (instead of Promise.all), which returns the result of all promises regardless of success/failure

Or, here's one I rolled myself

Promise.allSettled = function(arr) { 
    return Promise.all([].map.call(arr, 
        function(v) {
            return Promise.resolve(v).then(
                function fulfilled (x) { return {fulfilled: true,  value: x}; }, 
                function rejected  (e) { return {fulfilled: false, value: e}; }
            );
        })
    );
};

runOnecode would then look like

function runOnecode() {
    var str  = "";
    str += $(".google-visualization-controls-rangefilter-thumblabel").text();
    var until = str.substring(10);
    var from = str.substring(0,10);
    var center = $("#centerSelect option:selected").text();
    var query = [
        // your query data, removed for brevity ...
    ];
    Promise.allSettled(query.map(getData))
    .then(function(results) {
        // results is an array of results in the same order as query
        results.forEach(function(result) {
            if (result.fulfilled) {
                // successful
                console.log(result.value);
            }
            else {
                // unsuccessful
                console.log(result.value); // will show the thrown error
            }
        });
    });
}

@StevendeSalas would say that [Promises don't actually execute asynchronously](https://stackoverflow.com/questions/31241396/is-google-apps-script-synchronous#answer-60174689), and TheMaster [confirmed](https://stackoverflow.com/questions/62806366/run-function-asynchronously-in-google-apps-script/62807671#62807671). — Dan Dascalescu, Jul 09 '23 at 18:55

Using promises for mulitple queries to Google sheets

1 Answers1

Linked