Good day everyone. We make a file where we will keep the family budget. Faced the next challenge. There is a “referral” and “product category”. In one direction are collected certain categories of products. There is also an input form (such as a survey), so that we can clog up transactions through mobile phones. So, you need the script to analyze the category of products and automatically put a direction. Suppose the category of "metro", automatically put down the direction "transport" in the adjacent column. I wrote an example script that reads the contents of the current cell and puts the results in the first. The question is how to hang it all up 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() == "пиво"){ sheet.getRange("A1").setValue(SpreadsheetApp.getActiveRange().getValue());} // 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 a ready-made free solution bkper.com The application has several advantages and several options for integrating with Google Drive (loading scans from the Disk, importing values ​​into the Table via add-ons) - oshliaer

2 answers 2

Probably use an onEdit (event) trigger:

onEdit (event)

The cell spreadsheet is edited. It is a simple way to record that it was edited. This is a fact that it is being edited.

 function onEdit(event) { var ss = event.source.getActiveSheet(); var r = event.source.getActiveRange(); r.setComment("Last modified: " + (new Date())); }

    It's very simple, use the function onEdit (event) which only responds to changes in the data in the table. An example of a ready-made solution that, when changing any line, adds data to column number 8, the line number is the one that was changed by you

     function onEdit(event) { var sheet = event.source.getActiveSheet(); var sheetName = event.source.getActiveSheet().getSheetName() // Получаем имя листа который активен var actRng = event.source.getActiveRange(); var index = actRng.getRowIndex(); if (index > 1 && sheetName == "Менеджер") { Logger.log(event.source.parameters); //var user = Session.getEffectiveUser().getEmail(); var user = Session.getActiveUser().getEmail(); Logger.log(index); sheet.getRange(index, 8).setValue(user); }

    Based on your task, you need only to rewrite the condition iF.