How to statically save a dynamic value to another cell at a certain instant?-CodePudding

In my sheet I have a cell (let's say "A1") whose value changes dynamically by formulas on the same sheet.
I want to save the value that cell A1 contains at a given instant (say, 00:00 on 01/01/2023) in another cell (say, "B1").
Is it possibile? I have no experience with macros or Apps Script, but such a solution is nevertheless welcome.

CodePudding user response：

I believe your goal is as follows.

You want to copy the value of cell "A1" to "B1" without including the formula.
You want to run the script at a specific date.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet. And, please run installTrigger. By this, a time-driven trigger is installed. By this, the script of main is run at 00:00 on 01/01/2023 in your timezone.

function installTrigger() {
  const functionName = "main";
  const trigger = ScriptApp.getScriptTriggers().find(e => e.getHandlerFunction() == functionName);
  if (trigger) {
    ScriptApp.deleteTrigger(trigger);
  }
  ScriptApp.newTrigger(functionName).timeBased().at(new Date(2023, 0, 1)).create();
}

function main() {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const srcRange = sheet.getRange("A1");
  const dstRange = sheet.getRange("B1");
  srcRange.copyTo(dstRange, { contentsOnly: true });
}

Also, even when you directly run main, the script works. When main is run, in Sheet1, the cell "A1" is copied to "B1" without including the formula.

References:

CodePudding user response：

Run the trigger create function and it will call the save cell function every day near midnight. The saveCell function will save the value of A1 in A2

function saveCell() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet Name");
  sh.getRange("A2").setValue(sh.getRange("A1").getValue());
}


function createTimeBasedTriggerForSaveCell() {
  ScriptApp.newTrigger("saveCell").timeBased().everyDays(1).atHour(0).create();
}