Trigger all functions in a Sheet with Apps Script

Question

I want to pimp my private household google sheet with some functions. Therefore I wrote a function with a for loop to iterate over the rows 1-1000 to calculate how much money I spent. The function works and it calculates the numbers correctly. Everything is fine, but...

My Problem: After I added some values I have to reload the whole page to make the functions run again.

What I want: I would like to write a function that triggers a re-run of all functions in my sheet. Therefore I created a button that I can click.

What I tried:

SpreadsheetApp.flush();

as described here but it did not work. There was some processing but no value changed.

Edit 31.10.2017

This is the code I wrote. I would like to execute it everytime I edit the spreadsheet.

function sumValues(startRow, startColumn) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("MySheet");
  var range = sheet.getRange(startRow, startColumn, 1000);
  var data = range.getValues();

  var costs = calculate(data);
  return costs;
}

function calculate(data) {
  var cost = 0;
  var size = data.length;

  for(var i=0; i<size; i=i+1) {
    var current = data[i][0];
    if(typeof current == 'number') {
      cost = cost + current;
    }
  }
  return cost;  
}

Answer 1

Creating a menu and a function that will run when Spreadsheet is opened

If you write your function this way it will gather all of the data in your sheet into an array. You can perform your functions in this array very fast and then update the entire spreadsheet all at one time

function yourFunction()
{
   var ss=SpreadsheetApp.getActive();
   var sh=ss.getActiveSheet();
   var rg=sh.getDataRange();
   var vA=rg.getValues();//you now have an array of all values in your sheet
   for(var i=0;i<vA.length;i++)//if your data does start until row n then set i=n-1
   {
      //perform your line by line calculations here 
   }
   rg.setValues(vA);//Writes all of your changes all at once
}  

This will run your function whenever you open your spreadsheet so it will be ready for you to review it. Plus it also creates a dropdown menu named "My Tools" from which you can run it again.

When you're working with arrays remember that the indexes are zero based not one based so if you want to add column1 to column2 on every row it would be vA[i][0] + vA[i][1]. If you wish to add columns to spreadsheet to put calculation values in, then just add another header and the getDataRange() function always returns a non jagged array.

function onOpen()
{
  SpreadsheetApp.getUi().createMenu('My Tools').addItem('Run Function', 'yourFunction')
  yourFunction(); 
}

Answer 2

You can just put a trigger on OnEdit on the spreadsheet such that each time you edit something in the sheet, it will run that function. Google App Script

then add the SpreadsheetApp.flush();