CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
ASE
ASE

Reputation: 145

How to automatically add data to another sheet when a specific cell's values changes?

I have a sheet with the pricing of different products. These prices are updated very frequently, and I would love to find a solution whereby, whenever I change the date on cell B2, the date and pricing of the products are automatically recorded in a column in a different tab, called Archive.

Pricing Tab

enter image description here
(source: imggmi.com)

Archive File

enter image description here
(source: imggmi.com)

I have found several scripts that automatically record values in a different sheet based on specific values such as 'Done' or 'Completed' but I'm not sure how to adapt those scripts when the target value is always changing (i.e. cell B2).

One of those scripts are:

function onEdit() {
  var sheetNameToWatch = "Pricing";
  var columnNumberToWatch = 2;
  var valueToWatch = "[Unclear what the value should be]";
  var sheetNameToMoveTheRowTo = "Archive";
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();
  
  if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
    sheet.deleteRow(range.getRow());
  }
}

The full sheet can be found here: https://docs.google.com/spreadsheets/d/13UAx8ANRkvLcLZ7Pxj4INigQatFpv8F2NF6AVuRSPb8/edit?usp=sharing

The ideal output would be a script that can automatically record pricing values based on cell B2. If there's a simpler solution to this, it is very much welcome! Thanks in advance.

Upvotes: 3

Views: 1504

Answers (1)

Tanaike
Tanaike

Reputation: 201503

  • From your question, in your shared Spreadsheet, when the date cell of "B2" in the sheet of Current Pricing is changed, you want to copy the values of "B2:B" to the last column of the sheet of Archive.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

In this modification, I used the following flow using the event object of the simple trigger.

  1. When the cell is edited, the simple trigger of onEdit() is run.
  2. When onEdit() is run, when the sheet name, the edited cell and the value are sheetNameToWatch, dateCell and updated, respectively, the values of "B2:B" in the sheet of sheetNameToWatch is copied to the last column of the sheet of archiveSheet.

Modified script:

When you use this script, please set the variables of the top of script.

function onEdit(e) {
  var archiveSheet = "Archive"; // Please set this.
  var sheetNameToWatch = "Current Pricing"; // Please set this.
  var dateCell = "B2"; // Please set this.

  var range = e.range;
  var sheet = range.getSheet();
  var value = Number(e.value);
  if (
    sheet.getSheetName() == sheetNameToWatch &&
    range.getA1Notation() == dateCell &&
    Number(e.oldValue) != value
  ) {
    var srcRange = sheet.getRange(2, 2, sheet.getLastRow() - 1, 1);
    var dstSheet = e.source.getSheetByName(archiveSheet);
    var dstRange = dstSheet.getRange(2, dstSheet.getLastColumn() + 1);
    srcRange.copyTo(dstRange);
  }
}

Note:

  • This script uses the simple trigger. So when you use this script, in your shared Spreadsheet, please edit the cell of "B2" of sheetNameToWatch ("Current Pricing"). By this, the script is automatically run by the OnEdit event trigger.
    • If you directly run onEdit() with the script editor, an error occurs at var range = e.range;, because the event object is used. Please be careful this.

References:

If I misunderstood your question and this was not the result you want, I apologize.

Upvotes: 2

Related Questions