CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Digital Farmer
Digital Farmer

Reputation: 2127

Mode set to have the script make calls only every 2 minutes inside the every 1 minute trigger. (Google App Script)

function All() {
  var ss = SpreadsheetApp.getActive();

  ss.getRange('Página1!A6').setFormula('=IF(ISEVEN(MINUTE(NOW())),"Ok","Error")')
  ss.getRange('Página1!A6').copyTo(ss.getRange('Página1!A6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  if (ss.getSheetByName('Página1').getRange("A6").getValues()[0][0]=="Ok"){

    //History
    ss.getRange('Página1!D1').setFormula('=IFERROR(FILTER({C:C;H1},{C:C;H1}<>""))');
    ss.getRange('Página1!D1:D').copyTo(ss.getRange('Página1!C1'), 
  }
}

Google has triggers every 1 minute, 5 minutes, 10 minutes, 15 minutes and 30 minutes.

For this model, I use the 1 minute trigger!

To bypass this and be able to turn it on every 2 minutes instead of 1 minute (because it weighs a lot in the spreadsheet and occasionally creates errors), to deflect this I created this model where it analyzes if the minute of the current time is odd or even. If even, it activates the rest of the script, if odd it ends without doing anything else.

I would like to know if I could do this same thing, but instead of throwing the function into a cell, copy the value so that the formula NOW() doesn't keep updating all the time and so on ... same step but directly in the script, without moving the spreadsheet with unnecessary calls.

And if it would also be possible to do this to set the script to work every 3 minutes instead of 2 minutes as I managed to do.

Upvotes: 1

Views: 843

Answers (2)

ZektorH
ZektorH

Reputation: 2770

Instead of using a sheet with a formula to determine if the minute is even or odd, you can use the Apps Script alternative.

I am using the %(Remainder) operator to get the reminder of a division by 2. If it's zero then the number is odd.

The equivalent for MINUTE(NOW()) is achieved with the Javascript Date new Date().getMinutes()

function myFunction() {
  if (new Date().getMinutes()%2==0) { //If the minute is odd.
    //Your code here
  } //No need for else.  
}

Upvotes: 2

Wicket
Wicket

Reputation: 38296

Instead of modifying your spreadsheet use the Properties Service to store the last time you script ran. Bear in mind that the Properties Service only stores strings, so you will have to convert the Date object to an string an viceversa.

Related

Upvotes: 0

Related Questions