I think you can do all that, with the code below.
Code
function onEdit(e) {
var sName = e.source.getActiveSheet().getSheetName();
if(sName !== "Logging") {
var value;
var mA1 = e.range.getA1Notation().split(":")[0];
var time = new Date().toTimeString();
if(typeof(e.value) == 'string') {
var aCell = e.source.getRange(mA1);
value = aCell.getValue();
var form = "'" + aCell.getFormula();
} else {
value = e.value;
}
var data = [sName, mA1, time, value, form];
e.source.getSheetByName("Logging").appendRow(data);
}
}
Explained
The onEdit
will only work, if the active sheet isn't named Logging
. Next is to gather the wanted info and append it to the logger sheet. The range of the active cell needs to be a cell reference (A1
). A formula, with other references, will mix up the A1Notation
. Therefore the split(":")[0]
will take the first active cell reference (if you add a =
or +
) as range. If the cell happens to have a formula, then the value is obtained through the e.source
else through the e.value
. The formula is also obtained through the e.source
and stringified.
Screenshot
Example
I've created an example file for you: Logger Sheet
I do not know how to solve this problem without scripts, but there is a simple work around. When you update prices, you can drag them elsewhere in your sheet (e.g. below, in the form of a price record). The values on the 'Sales' sheet will stay linked to the original price, and the formula you enter for sales will be the same.
If you choose this work around, I would recommend creating a script that automatically moves your prices so that you don't have to manually drag things around and so that the newest prices appear closer to the top. There are many ways to make that type of script, but here's one not so elegant, but simple way:
var ss = SpreadsheetApp.getActiver();
// Many of these values depend on the layout of your document.
// I have indicated where you will need to change values if
// you change the layout.
function onOpen(){
// This will make a menu appear that allows you to run the scripts
var ss = SpreadsheetApp.getActive();
var items = [
{name: 'Make space for a new price', functionName: 'newprice'},
{name: 'New sales day', functionName: 'newsales'},
];
ss.addMenu('New Entry', items);
};
function newprice(){
// This will simply insert a row under your prices.
// You just have to drag the on you want to change into the new row
// Your info on the other sheets will stay linked to the correct number
var sheet = ss.getSheets()[1]; // This number may need editing
sheet.insertRowBefore(7); // This number may also need attention
};
function newsales(){
var sheet = ss.getSheets()[0];
var where = 7; // Where to add the new lines?
var how_many = 5; // How many products to add?
for (var ind = 0; ind < how_many; ind ++){
sheet.insertRowBefore(where);
};
// Copy the values
var source = sheet.getRange("A3:G7"); // This would need attention
var destination = sheet.getRange("A8:G12"); // And this...
source.copyValuesToRange(destination,1,1,1,1);
};
Using a script may be more trouble than it's worth for this type of problem.
If for some reason (I can't imagine why) you want to have one cell that adds old prices and new prices, then you need a more advanced script. It would be easier to make one cell associated with each price, and then add them in the final cell.
Best Answer
I figured the answer finally:
onEdit
, as a simple trigger can't access advanced services (like slides)UrlFetchApp
call with proper OAuth authentication, like that: