Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to execute multiple functions in google apps script in order?

Thanks in advance for your time here. I am self taught and new to coding, hence apologies if the question below is something an average programmer should already know.

I have created multiple functions in a single gas file, which when run individually calculate various aspects of same expenses data. But I need all functions to run in order to reach my end goal, which unfortunately I am unable to achieve. Please see examples below.

  1. Both functions when Run individually work without issues.

    function totalSpend(){
    var ss = SpreadsheetApp.getActiveSheet();
    var rt = ss.getRange("K2").getValue();
    var pt = ss.getRange("K1").getValue();
    var ts =  rt + pt ;
    ss.getRange("K3").setValue(ts); }
    
    function perHead(){
    var ss = SpreadsheetApp.getActiveSheet();
    var ts = ss.getRange("K3").getValues();
    var ph = ts / 2;
    ss.getRange("K4").setValue(ph);}
    
  2. But if I combine them both under a single function and then run, nothing happens. I tried using debugger and Execution Transcript but didn't have any luck. Below is example which doesn't work.

    function expCalc(){
    
    function totalSpend(){
    var ss = SpreadsheetApp.getActiveSheet();
    var rt = ss.getRange("K2").getValue();
    var pt = ss.getRange("K1").getValue();
    var ts =  rt + pt ;
    ss.getRange("K3").setValue(ts); };
    
    function perHead(){
    var ss = SpreadsheetApp.getActiveSheet();
    var ts = ss.getRange("K3").getValues();
    var ph = ts / 2;
    ss.getRange("K4").setValue(ph);};
    
    }
    
like image 391
rakesh bhat Avatar asked May 09 '15 15:05

rakesh bhat


People also ask

How do I run multiple scripts in Google Sheets?

You can have multiple scripts in the same script file and you can have multiple script files as well. If you click on the small downward-facing arrow at the right of the Script file name, it will show you options to rename, delete, and create a copy of the script file.

How do I concatenate in a Google script?

To concatenate two strings (i.e., to join them together), use the concatenation operator ( + ). You can append a value to a string by using the += operator.

How does onEdit work?

The onEdit(e) trigger runs automatically when a user changes the value of any cell in a spreadsheet. Most onEdit(e) triggers use the information in the event object to respond appropriately. For example, the onEdit(e) function below sets a comment on the cell that records the last time it was edited.

How do I trigger an email in Google Sheets when a cell value changes?

Create “OnEdit” Trigger triggerOnEdit() is the function that would be called when the Google Sheet is edited. It calls the function showMessageOnUpdate() just below that and it will just throw a message box and show the range that was edited.


1 Answers

function expCalc(){
  totalSpend();
  perHead();
}

function totalSpend(){
var ss = SpreadsheetApp.getActiveSheet();
var rt = ss.getRange("K2").getValue();
var pt = ss.getRange("K1").getValue();
var ts =  rt + pt ;
ss.getRange("K3").setValue(ts); }

function perHead(){
var ss = SpreadsheetApp.getActiveSheet();
var ts = ss.getRange("K3").getValues();
var ph = ts / 2;
ss.getRange("K4").setValue(ph);}

or

function expCalc(){

var ss = SpreadsheetApp.getActiveSheet();
var rt = ss.getRange("K2").getValue();
var pt = ss.getRange("K1").getValue();
var ts =  rt + pt ;
ss.getRange("K3").setValue(ts);

var ts = ss.getRange("K3").getValues();
var ph = ts / 2;
ss.getRange("K4").setValue(ph);
}
like image 155
ScampMichael Avatar answered Oct 22 '22 01:10

ScampMichael