Script in Google Spreadsheets. How to track event changes to a cell?

The question was asked: 6 years 11 months ago   views: 23

Good afternoon everyone. Make a file where we keep the family budget. Faced with the next problem. There is a "direction" and "category products". In one direction is collected by certain category of products. There is also a form of input (type of survey) that we could score transactions through mobile phones. So, you need to make a script analyze a product category and a gun was put down by the direction. For example, the category "metro", a gun is put in the adjacent column direction "transport". I wrote a sample script which reads the contents of the current cell and enters the results in the first. The question is, how does it all hang on the input event (hit enter - the script worked)

function myFunction() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
// var first = Browser.inputBox("First value");
 if (SpreadsheetApp.getActiveRange().getValue() == "beer"){
// sheet.getRange("A1").setValue("First value:");
// sheet.getRange("B1").setValue(first);
// var next = Browser.inputBox("Next value"); 
// sheet.getRange("A2").setValue("Next value:");
// sheet.getRange("B2").setValue(next);
// var result = sheet.getRange("B1").getValue() + sheet.getRange("B2").getValue();
// sheet.getRange("A3").setValue("Result:");
// sheet.getRange("B3").setValue(result);
// Browser.msgBox("Summ is:" + result);
 ss.addMenu("Test", [{name: "Test", functionName: "myFunction"}]);
In this case, it is possible to use ready-made free solution the App has a number of advantages and several options for integration with Google Drive (upload scans from Disk, the import values to the Table using additions) - oshliaer oshliaer 21-04-2015 в 17:48:59

Answers   2


You probably use a trigger onEdit(event):


The onEdit function runs automatically when any cell of the spreadsheet is edited. A very simple use case for onEdit is to record the last modified time in a comment on the cell that was edited. The e argument that is passed in to the function contains a single property, source , which is the spreadsheet that is being edited.

function onEdit(event)
 var ss = event.source.getActiveSheet();
 var r = event.source.getActiveRange();
 r.setComment("Last modified:" + (new Date()));
Answered: Ilya Pirogov Ilya Pirogov 21-06-2012 в 13:51:36

It's very simple, use the function onEdit(event) which only responds to the data in the table changes. An example of a solution that change when any row enter the data in column number 8 line number that was changed by You

function onEdit(event) {

 var sheet = event.source.getActiveSheet();
 var sheetName = event.source.getActiveSheet().getSheetName() // Get the name of the sheet that is active 
 var actRng = event.source.getActiveRange();
 var index = actRng.getRowIndex();

 if (index > 1 && sheetName == "Manager") {
 //var user = Session.getEffectiveUser().getEmail();
 var user = Session.getActiveUser().getEmail();
 sheet.getRange(index, 8).setValue(user);

Based on Your problem You only have to rewrite the iF condition.

Answered: santer santer 22-04-2015 в 00:10:51