'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
(source: imggmi.com)
Archive File
(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.
Solution 1:[1]
- 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 ofArchive
.
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.
- When the cell is edited, the simple trigger of
onEdit()
is run. - When
onEdit()
is run, when the sheet name, the edited cell and the value aresheetNameToWatch
,dateCell
and updated, respectively, the values of "B2:B" in the sheet ofsheetNameToWatch
is copied to the last column of the sheet ofarchiveSheet
.
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 atvar range = e.range;
, because the event object is used. Please be careful this.
- If you directly run
References:
If I misunderstood your question and this was not the result you want, I apologize.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 |