Search code examples
google-apps-scriptgoogle-sheetscustom-function

Google spreadsheet - Snapshot values from a columnt that depend on another script

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!

Solution

  • 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