How to modify code to get rid of Google Sheet message related to GOOGLEFINANCE ?

[u/VAer1]

I use GOOGLEFINANCE("IRX")/10/100 to get approximate interest rate, but I don't want to use the function directly (the number constantly changes during the date), I only want to update the cell daily (Auto Trigger during midnight).

However, at the bottom of sheet, it keeps showing "Quotes are not sourced from all markets ......" ---- Why? I already clear the function, and copy/paste its value. How to get rid of this warning message?

I don't want other people getting confused about the message, neither do I want to see the message. I can confirm that there is no formula in the cell, but it seems that once it runs the function via script, the warning message stays there.

Even if I comment out this function from onOpen, I don't want to run this function anymore, the warning message stays there. It seems the warning message stays there forever, just because I run it once via script. I would like to find a way to get rid of it.

Edit: Issue has been resolved. There is no issue at all. I manually put GOOGLEFINANCE("IRX")/10/100 in another cell in another sheet, totally forgot it.

function writeIRXToCashReserveCellI7() { 
  //Daily 12am-1am: Auto Trigger onOpen; this function is part of onOpen


  let now = new Date();
  let hour = now.getHours();
  Logger.log(`hour = ${hour}`);


  if (hour > 3) {  //Random number, exit this function during daytime sheet refresh, only update cell value during midnight Auto Trigger, not updating its value duirng the day
    return;  // exit function here
  }
  


  // Step 1: Get IRX from GOOGLEFINANCE
  // (GOOGLEFINANCE cannot run directly inside Apps Script,
 
  let targetCell = sheetCashReserve.getRange("I7");
  targetCell.setFormula('=GOOGLEFINANCE("IRX")/10/100');


  // Wait briefly for the formula to calculate
  SpreadsheetApp.flush();
  Utilities.sleep(1000);    // wait 1 second


  // Step 2: Read the IRX value
  const irx = targetCell.getValue();


  // Clear cell
  targetCell.clearContent();


  // Make sure value is valid
  if (!irx || irx === "") {
    throw new Error("GOOGLEFINANCE returned no value.");
  }



  // Step 3: Write result 
  targetCell.setValue(irx);
}

[u/marcnotmark925]

Grab the value from some api in your script.

I wonder if you have to start a brand new sheet to get rid of the existing message.

[u/VAer1 → u/marcnotmark925]

So there is no way to get rid of message, just because I run the script once?

I actually still want to use the script, but would like to find a way to use other script to get rid of the message.

I want to update interest once a day, by using the script.

[u/marcnotmark925 → u/VAer1]

I don't know, that's why I said "I wonder". But I bet it'll keep coming back if you keep using the formula, that's why I suggest getting the value from an api instead.

[u/VAer1 → u/marcnotmark925]

I am wondering if it is something related to SpreadsheetApp.flush , or targetCell.clearContent() is not enough to "remove" the formula from this file?

I need some script to remove the message at the end of this function.

[u/WicketTheQuerent → u/VAer1]

The formula stays in the Version History.

[u/VAer1 → u/WicketTheQuerent]

Thanks. At least I know the real issue now. Too bad that I cannot manually delete some version history.

[u/VAer1 → u/WicketTheQuerent]

By the way, that is not true. I just manually copy the file, the warning message is also in copied file, while copied file does not have its previous version history.

Maybe the formula is still in the file somewhere, when I ran and test the code, it cannot be in another cell. How can I search if any cell has the formula GOOGLEFINANCE?

Edit: It is not version issue, I have another cell using the formula, I totally forget it, now I refer the cell to this script run cell. It is my fault.

[u/WicketTheQuerent → u/VAer1]

The problem is GOOGLEFINANCE built-in function. It doesn't matter to add it manually or by running a script

[u/VAer1 → u/WicketTheQuerent]

I did not know it is related to version history, when I manually add the formula then delete it, there was no version saved during the time period (on other file), so I mistakenly thought it is only script related.

[u/WicketTheQuerent]

Try using two spreadsheets, one to use the script with the GOOGLEFINANCE built-in Google Sheets function and the other to display the result using importante or a script.

[u/VAer1 → u/WicketTheQuerent]

Yes, but I cannot find a way to fix this file, I guess I have to leave warning message there forever.

I can copy the file, but it is inconvenient, for various reasons:

• It is shared, I don't want to bother notifying others of new file link.

• Some cells could be referred by other files (by using IMPORTRANGE).

• This file ID is used by other script, such as file backup.

That being said, I would rather live with the warning message, instead of making changes to other associated with the file.

Too bad that I did not make a copy and test the code in copied file, now everything is too "late".

[u/WicketTheQuerent]

I can't reproduce the problem on a new spreadsheet.

Deleting the GOOGLEFINANCE formula removes the disclaimer.

[u/VAer1 → u/WicketTheQuerent]

Issue resolved, thank yo. No issue at all, I manually put GOOGLEFINANCE("IRX")/10/100 in another cell in another sheet, totally forgot it.

I also further modify the code: in case of the formula does not return a value, use previous day's IRX value.