CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Octotroph
Octotroph

Reputation: 3

Is there a way to automatically run a formula a single time?

Let's say I have a table:

Quantity Item Value Fetched Item Suggestion
5 Meat Variable Meat ...

The cells in the Value column have a VLOOKUP formula to fetch the value of items with the same name as "Item", and returns "Variable" if there isn't a good fit (which means there are multiple options). The Cells in the Suggestion column have a VLOOKUP formula to fetch the lowest value item in a category with the same name as "Item", and returns "" if there isn't such a category (which means the item would have been found in the value column's search). After the search, the table looks like this:

Quantity Item Value Fetched Item Suggestion
5 Meat Variable Meat Cow Meat

Now, columns D and E aren't going to be used, so the user changes column B to have the suggested item.

Quantity Item Value Fetched Item Suggestion
5 Cow Meat 300 Meat Cow Meat

So I don't want the user to have to put "meat" in the "Fetched Item" column for it to work, so let's say that column just has a simple =B2 formula in it. The sheet now looks like this:

Quantity Item Value Fetched Item Suggestion
5 Cow Meat 300 Cow Meat

However, the sheet that the Suggestion formula references updates the values regularly, so fast forward to the point where cow meat now costs more than pig meat. I want the table to look like this:

Quantity Item Value Fetched Item Suggestion
5 Cow Meat 300 Meat Pig Meat

This way, the user can update column B accordingly. This means that I need some way for column D to automatically copy the content of column B if column C reads "Variable", while ignoring future changes to column B.

=IF($C5="Variable",[copy $B5],"")

Something like this, I guess? I guess the easiest way to put this is "Is there a way to automatically run a formula a single time?", which is better than my old title of "Take a "snapshot" of data when referencing a cell so it doesn't change if the referenced cell does?", so I've changed it accordingly.

Upvotes: 0

Views: 547

Answers (1)

Iamblichus
Iamblichus

Reputation: 19339

Using onEdit:

You could use an onEdit trigger, using Apps Script (click Tools > Script editor, copy the function below and save the project).

(For this to work, please create a new column (G), which will be used to store information on whether there were previous copies. You can then hide it.)

This function would execute every time a user edits the spreadsheet, and it would do the following, using the event object:

  • Check whether edited column corresponds to Item and edited column is your desired sheet (column C and sheet Recipe Cards in the spreadsheet you provided).
  • If that's the case, check whether Value column (D) is equal to Variable and column G is equal to a certain string (called Avoid future updates in the sample below).
  • If that's the case, set the value from C to E and write Avoid future updates to column G.

Code snippet:

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const rowIndex = range.getRow();
  const colIndex = range.getColumn();
  const avoidUpdateColumn = 7;
  const itemColumn = 3;
  const valueColumn = 4;
  const fetchedItemColumn = 5;
  const copyValue = "Variable"; // Do not copy if column doesn't have this value
  const avoidUpdateValue = "Avoid future updates"; // Do not copy if column has this value
  const sheetName = "Recipe Cards";
  if (colIndex === itemColumn && sheet.getName() === sheetName) {
    const numCols = sheet.getLastColumn()-colIndex+1;
    const rowValues = sheet.getRange(rowIndex, colIndex, 1, numCols).getValues()[0];
    if (rowValues[valueColumn-itemColumn] === copyValue && rowValues[numCols-1] !== avoidUpdateValue) {
      sheet.getRange(rowIndex, fetchedItemColumn).setValue(rowValues[0]);
      sheet.getRange(rowIndex, avoidUpdateColumn).setValue(avoidUpdateValue);
    }
  }
}

Reference:

Upvotes: 0

Related Questions