How Stop Formula from Using Updated Data? (Google Sheets)

Question

I have a vlookup formula looking for names and returning the average for that person. I record these averages throughout the week, and identify them as W1, W2, W3, etc. in different columns. The problem is, the data I import only has ONE average on it, meaning that if I import it, it will override what I had already put in a few days ago using the same report, just an older version.

My question is, is there a way for me to stop the formula for W1 from updating when the data it is calling on changes? Essentially, to freeze the values? That way I don't have to keep adding new tabs to import the new data in an effort to save the history. The data is robust, and I am gathering more info than just averages, so I need the whole thing.

I wouldn't mind scripting something too that would help solve my issue, I just need some guidance.

Edit: thoughts on scripting a menu button that would copy, paste values only of a selected range that I could trigger right before I update the data?

I want to be able to select a range in my sheet, select the menu "Save Info", have it copy what I selected, and paste it right back where it already was. This will remove the formulas that would otherwise cause my values to change upon the new data import, and leave my history intact.

The following is what I have come up with so far, but I receive this error:

"TypeError: sourceSheet.getDataRange is not a function".

function saveInfo(){
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = spreadSheet.getActiveSheet
  
  var sourceRange = sourceSheet.getDataRange();
  
  var targetSheet = spreadSheet.getActiveSheet();
  
  sourceRange.copyTo(targetSheet.getActiveRange());
} 


function updateMenu() {
  SpreadsheetApp.getActiveSpreadsheet().updateMenu('Save Info', generateMenu())
};
function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu('Save Info', generateMenu());
};
function generateMenu() {
  var entries = [{
    name: "Save Data Before Update",
    functionName: "saveInfo"
  }];
  
  return entries;
}

Any ideas??

Thanks!

user_14265469 · Answer

Here you go:

function saveInfo() {
    var range = SpreadsheetApp.getActiveSheet().getActiveRange();
    var values = range.getDisplayValues();
    range.setValues(values);
}

function onOpen() {
  SpreadsheetApp.getUi()
  .createMenu('Save Info')
  .addItem('Save Data Before Update', 'saveInfo')
  .addToUi();
}

The script converts data inside selected cells into a plain text.

But actually you don't need any script. You can just select the cells, press Ctrl+C and Ctrl+Shift+V.

How Stop Formula from Using Updated Data? (Google Sheets)

1 Answers