0

I've a spreadsheet that uses a function from another external script (https://github.com/Eloise1988/CRYPTOBALANCE) which grabs the balance from a wallet.

I want to snapshot this value daily on another column, so I've created the following script:

function snapshot() {
  SpreadsheetApp.flush()

  // Assign 'dashboard' the Dashboard sheet.
  var Carteiras = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Carteiras');

  attempts = 0;
  while (
    (Carteiras.getRange("C2:C2").getValue() === '#NAME'
    || Carteiras.getRange("C2:C2").getValue() === "#NAME?" 
    || Carteiras.getRange("C2:C2").getValue() === 'Loading...'
    || Carteiras.getRange("C2:C2").getValue() === 'Contact for BALANCE : t.me/TheCryptoCurious')
    && attempts < 60
  ) {
    console.log('Formula is not yet ready... re-attempting in 1seg');
    console.log('C2 value is ', Carteiras.getRange("C2:C2").getValue());
    Utilities.sleep(1000)
    attempts++;
  }

  console.log('C2 value is ', Carteiras.getRange("C2:C2").getValue());

  if (attempts < 60) {
    Carteiras.getRange("D2:D23").setValues(Carteiras.getRange("C2:C23").getValues());
    console.log('Values updated successfully!');
  } else {
    console.error('Failed to grab the formula values.');
  }
}

This script basically attempts to grab the balance from the wallet (Columns C2:C) , i know that once C2 is loaded all the others are loaded too, so I'm checking that C2 is in a valid state (e.g.: Not loading, no #Name or anything)

I've set a time driven trigger to run this snapshot function every day in the morning (10am to 11am) -- The problem is that the column is always on #NAME?

enter image description here

I think at some point google is not allowing this other external script to run, any ideas how can i make sure how to run this other script?

Also any improvements on my code will be welcomed as i never did anything on google spreadsheets.

Appreciated!

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Luis Lopes
  • 506
  • 4
  • 14
  • Are you doing something like in cell C2 `=CRYPTOBALANCE("TICKER","ADDRESS")`? If so these are asynchronise functions which means unless you implement a Promise they will run and Google Sheets doesn't wait for it to finish before displaying itself. – TheWizEd Feb 11 '22 at 17:33
  • Try 30s sleep.. – TheMaster Feb 11 '22 at 18:15
  • 60s seems to be already long enough, this take ~1/2sec to load. @TheWizEd that's why i am waiting 60 seconds. Could expect "Loading..." or something else, not "#NAME?" error. – Luis Lopes Feb 11 '22 at 20:02
  • @TheMaster 60 seconds seems to be already a lot... I am sleeping that many seconds 1 sec a time for 60 seconds trying to grab a value from that column – Luis Lopes Feb 11 '22 at 20:03
  • Try 60s in a single wait anyway. If that doesn't work, see if my answer [here](https://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-sheet) works. – TheMaster Feb 11 '22 at 20:42
  • @TheMaster got an error from google saying "Exceeded maximum execution time" so i don't think that will work. – Luis Lopes Feb 14 '22 at 10:29
  • 1
    What's the formula in C2 and does it return a value manually? – TheMaster Feb 14 '22 at 11:20

1 Answers1

2

Instead of trying to read the result of custom function from the spreadsheet, call the custom function as a "normal" function.

function snapshot(){
   const spreadsheet = SpreadsheetApp.getActiveSpreadshet();
   var Carteiras = spreadsheet.getSheetByName('Carteiras');
   const values = Carteiras.getRange("C2:C23").getValues();
   const balance = values.map(row => {
     const ticker = 'a-ticker'; // or use row[some-index-1] or other way to get the ticker
     const address = 'a-address'; // or use row[some-index-2] or other way to get the address
     const refresh_cell = null; // not needed in this context
     return [CRYPTOBALANCE(ticker,address, refresh_cell)]
   }); 
 
   Carteiras.getRange("D2:D23").setValues(balance);
}

The above because Google Apps Script officials docs have not disclosed how exactly the formula recalculation works when the spreadsheet is opened by the script when the spreadsheet has not been first opened by the active user as usually occurs when a daily time-driven trigger is executed. I guess that the custom functions are loaded into the active spreadsheet function list when the formula recalculation is triggered by Google Sheets web client-side code.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166