Databases
StackOverflow2013
Postsdbo
POHow can I run a google script automatically on multiple sheets in the same spreadsheet
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I run a google script automatically on multiple sheets in the same spreadsheet
    primarykey
    data
    text
    <p>I have the following <a href="https://stackoverflow.com/questions/16600972/insert-the-result-count-number-of-days-between-two-days-in-spreadsheet-using-goo">script</a>.</p> <h1>Situation:</h1> <p>I have a spreadsheet with 10 worksheets and 15 users logging in and modifying it.</p> <h1>Script Function:</h1> <blockquote> <p>Inserts the number of days between two days in the activesheet in a specific column, Script compare date from column J and TodayDay and inserts the difference in the column F.</p> </blockquote> <h1>Problem:</h1> <blockquote> <p>I can not run this script with using timed triggers. I want to run this script every 15 min for the concurrent sheet.</p> </blockquote> <h1>Test Case:</h1> <blockquote> <p>The trigger only runs with timing if I have only one sheet in the spreadsheet. The trigger doesn't run with timing if I have more than one sheet. The trigger only runs for more than one sheet if I set the same trigger OnEdit.</p> </blockquote> <p>I need to run this script for all sheets or active sheet every 15mins because I have two other scripts running OnEdit. When I set this script also in OnEdit the spreadsheet turn slowly.</p> <pre><code> function onOpen() { var menuEntries = [ {name: "UpdateAge", functionName: "toTrigger"}, ]; var ss = SpreadsheetApp.getActiveSpreadsheet(); ss.addMenu("Tools",menuEntries);// } // create a timer trigger that will call "toTrigger" every 15 minutes function toTrigger(){ var sh = SpreadsheetApp.getActiveSheet(); var data = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getValues(); for(var n=0;n&lt;data.length;++n){ if(typeof(data[n][9])=='object'){ data[n][5]=dayToToday(data[n][9]) } } sh.getRange(1,1,data.length,data[0].length).setValues(data) } function dayToToday(x){ var refcell = x;;// get value in column A to get the reference date var refTime = new Date(refcell); var ref = refTime.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days var today = new Date(); var TD = today.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days var day = parseInt(TD-ref);// get the difference in days (integer value ) return day ; // return result that will be in cell } </code></pre> <h1>EDIT: WORKING</h1> <p>Hi Srik,</p> <p>Thanks so much Now is Working.</p> <pre><code> function onOpen() { var menuEntries = [ {name: "UpdateAge", functionName: "shellFunction"}, ]; var ss = SpreadsheetApp.getActiveSpreadsheet(); ss.addMenu("Tools",menuEntries);// } function shellFunction(){ var sheets = ['Sheet1','Sheet2','Sheet3','Sheet4']; for (var s in sheets){ toTrigger(sheets[s]); } } // create a timer trigger that will call "toTrigger" every 15 minutes function toTrigger(sheetName){ var ss = SpreadsheetApp.openById('SHEET ID'); var sh = ss.getSheetByName(sheetName); var data = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getValues(); for(var n=0;n&lt;data.length;++n){ if(typeof(data[n][9])=='object'){ data[n][5]=dayToToday(data[n][9]) } } sh.getRange(1,1,data.length,data[0].length).setValues(data) } function dayToToday(x){ var refcell = x;;// get value in column A to get the reference date var refTime = new Date(refcell); var ref = refTime.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days var today = new Date(); var TD = today.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days var day = parseInt(TD-ref);// get the difference in days (integer value ) return day ; // return result that will be in cell } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USLAD Service Desk
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow can I run a google script automatically on multiple sheets in the same spreadsheet
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COWhy not use a spreadsheet function? You can calculate the day difference for each row without the fuss. Just remember to send the reference date and toda's date as a function parameter.
      singulars
      1. POHow can I run a google script automatically on multiple sheets in the same spreadsheet
      1. USTaras
    2. COI can not use a formula Array for ex because the spreadsheet is under modification every time. When the activity on that row is completed we delete it. <br> Example: If we use formula someone need to drag the to the new row, to avoid any error or a complex sheet I use this script to automatically insert the number without any issue is someone delete the cell where the formula is. <br> Using Array formula but when you add new row the formula not drag automatically also using array. <br> For my case I can not implement a formula. If you have any suggestion plese let me know.
      singulars
      1. POHow can I run a google script automatically on multiple sheets in the same spreadsheet
      1. USLAD Service Desk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload