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

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

Content Index :

JavaScript asynchronous function Google Sheet API, data availability is delayed due to being a formula retrieval? How to
Tag : javascript , By : 03B
Date : November 29 2020, 12:01 PM

This might help you 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. , 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) {

No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

How to simulate the result of a Google Sheet multiplication formula in Javascript?

Tag : javascript , By : lili
Date : March 29 2020, 07:55 AM
help you fix your problem After revisiting this I have updated the jsFiddle.
The main components of what I believe are a satisfactory solution are:
var my_decimal_js_value = new Decimal(original_value).times(new Decimal(multiplier)).toDecimalPlaces(4, 7);
function trailingZeros(my_decimal_js_value) {
  var result = my_decimal_js_value; 
  // add zeros if required:
  var split_result = result.toString().split(".");
  // if there are decimals present
  if (split_result[1] != undefined) {
    // declare trailing_zeros;
    var trailing_zeros;
    // get the amount of decimal numbers
    decimals_present = split_result[1].length;
    // if one decimal number, add three trailing zeros
    if (decimals_present === 1) {
      trailing_zeros = "000";
      result += trailing_zeros;
    // if two decimal numbers, add two trailing zeros
    else if (decimals_present === 2) {
      trailing_zeros = "00";
      result += trailing_zeros;
    // if three decimal numbers, add one trailing zero
    else if (decimals_present === 3) {
      trailing_zeros = "0";
      result += trailing_zeros;
    // if four decimal numbers, just convert result to string
    else if (decimals_present === 4) {
      result = result.toString();
  // if there are no decimals present, add a decimal place and four zeros
  else if (split_result[1] === undefined) {
    trailing_zeros = ".0000";
    result += trailing_zeros;

  return result;

Converting a google sheet formula into a custom function

Tag : google-apps-script , By : Oli
Date : March 29 2020, 07:55 AM
this one helps. It's better if you'll break it down into the various functions and build a formula from multiple custom functions. As you've said, the performance will be slow if you're working on a sheet with lots of filter formulas.
Here are some references which may help:

Importing data with formula from another google sheet and deleting original data

Tag : google-apps-script , By : user130518
Date : March 29 2020, 07:55 AM
seems to work fine Issue: Formulas are not retrieved with getValues() Possible Solutions: Use range#getFormulas and range#getValues and mix those two arrays to create a array of formulas and values to setValues() later. Use Advanced Google Services to access Sheets API to directly get the mix. Sample Script Snippets:
  var data = range.getValues();
  data = range.getFormulas().map(function(e, i) {//i=index of row(e)
    return e.map(function(f, j) {//j = index of column(f)
      return f === "" ? data[i][j] : f;
  var req = {
    ranges: 'results!2:688',
    valueRenderOption: 'FORMULA', //get formulas with values
  var r = Sheets.Spreadsheets.Values.batchGet('Sheet 1 ID', req);
  data = r.valueRanges[0].values;

How do i translate SORT and Match google sheet formula into app script/ javascript?

Tag : javascript , By : Andrew L.
Date : March 29 2020, 07:55 AM
will be helpful for those in need How do I run a formula like: , To sort your sheet, you can use the following code:
function myFunction() {
  var inSheet = SpreadsheetApp.getActive().getSheets()[0];
  var outSheet = SpreadsheetApp.getActive().getSheets()[1];

  var values = inSheet.getRange('A2:C23').getValues();


function mySort(row1, row2) {
  var values = ["QTA 10 Days","QTA-REMAKE","QTA-SAMPLE","QTA NEW PROJECT","QTA NPI-CVF-Bryan","Qual-QTA-NPI Remake"];
  var val1 = values.indexOf(row1[2]);
  var val2 = values.indexOf(row2[2]);
  return val1 - val2;
function mySort(row1, row2) {
  var val1 = row1[2];
  var val2 = row2[2];
  var qta1 = val1.indexOf("QTA") == 1;
  var qta2 = val2.indexOf("QTA") == 1;
  if (qta1 === qta2) return  strcmp(val1, val2);
  return qta1 ? 1 : -1;

function strcmp(a, b) {
  if (a.toString() < b.toString()) return -1;
  if (a.toString() > b.toString()) return 1;
  return 0;

Always got Formula parse error when trying to use QUERY function on google sheet

Tag : google-sheets , By : Mossy Breen
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • Why several clicks fire for each instance of a Class?
  • cant grab value from div using attr()
  • Make web service not accessible by browser?
  • Select DOM elements within template
  • How to customize angular ui-grid row background color?
  • Binding data in two respective $scope in AngularJS, how?
  • react put some props after import class
  • Variable is distributed among same components
  • switch class in jQuery/JavaScript for active/inactive state
  • How To Track Outbound Clicks Using Javascript
  • How to get total seconds elapsed since the beginning of the month?
  • Protractor: Stale element reference at "browser.wait(EC.visibilityOf(confDial), FIFTY_SECONDS);"
  • Access to an array inside an object
  • Reseting an JavaScript Interval with different value?
  • Learning Angular for the first time - version 2 or 4?
  • Rendering RSS feed (XML) in React with jquery, why do object values parsed from the feed seem empty?
  • Node.js xml2js http.request tag matching
  • applying texture to custom plane geometry in Threejs
  • How do I pass an Event as a function parameter in HTML written inside JavaScript variable?
  • Interface of object parameter with default value and default property
  • Does using try / catch rather than `.catch` observable operator with XHR requests reduce performance?
  • In IE11 background colors not working on printing
  • TypeScript interface signature "(): string"
  • Is synchronous XMLHttpRequest supported in Internet Explorer 10 and 11?
  • Axios function not returning any values with js
  • How to use firestore TIMESTAMP to create time-stamp in JavaScript
  • Instantiate subclass without constructing
  • Outputing a created object in javascript to a server side JSON file
  • Transition the numeric labels in a bar chart
  • createBottomTabNavigator: hide just one tab from the tabBar
  • iife vs simple statement behavior with document.title
  • showDialogPopup Refresh Page after Click
  • Child component does not set the initial value passed from the parent: ReactJS
  • Nodejs javascript added to button not responding
  • Trying to define a promise.all
  • Python GEE to extract featurecollection timeseries from Landsat imageries
  • How to write negative binary number?
  • Unable to set property of individual object
  • Why Named Function Expression itself cannot assign Name to another Value?
  • How to reset scroll position on single page pagination
  • How to scroll to bottom of page, when a new message is sent - Socket.io
  • How to reconstruct audio blob from a base64 encoded String?
  • How to calculate numbers within an array of objects
  • Using JavaScript to change CSS properties of SVG circles for all circles that was not clicked
  • Is there value in propTypeing repeat required properties at every level of nested components?
  • Call function if variable does not exist in a filter
  • localStorage value doesn't get updated automatically
  • React not updating state with setState correctly inside promises
  • Direct native JavaScript or jQuery method to get containing block of an element
  • Dynamic JSON object to Html Table
  • How to send Id and file to same controller?
  • How to Make a jQuery Table Cell(td) clickable to Run a Function?
  • Prevent the duplicate function onchange event jQuery
  • Reactjs Input Upload doesn't return local URL
  • An unexpected 'StartObject' node was found when reading from the JSON reader. A 'PrimitiveValue' node was expected
  • How to filter a JSON Array, with an eventListener input value at keyup
  • How to separate the different properties of filter in css?
  • Why dont work change event of bootstrap input spinner?
  • How to hide overflow text content in react
  • Is there another way to write line 6
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com