Google Sheets – How to Set a Trigger to Run Every Second

google sheetsgoogle-apps-script

While searching for answer to How can I trigger a function when switching sheets within a spreadsheet? I noticed that the Triggers for current project dialog has an option for specifying a trigger interval in seconds.

That is, in the first drop-down menu (a), I select On time, and in the next (b) I select Timer, seconds (a):

Now I would expect the drop-down menu c to contain the items Every second, Every 2 seconds, Every 4 seconds and so on. However, it lists the options Every hour, Every 2 hours etc – as if I had selected Timer, hours in a.

This looks like a bug, right? Or is there another way to set up a trigger that runs every X seconds?

Best Answer

Using English as the main language in the Google account settings the options for the second drop-down (b) are

Minutes timer
Hour timer
Day timer
Week timer
Month timer

There isn't a seconds timer, so it's very likely that there is localization error. Use the Google Apps Script Issue Tracker to report it to Google.

Related Solutions

Google-sheets – Trigger function when IMPORTRANGE() is updated

NOTE: Answer to revision 4

Google Apps Script on edit events are triggered by user made changes only, they aren't triggered on automatic changes like does that occur to automatic recalculation of import functions and functions like NOW. By the other hand, in order to make a change on an external sheet, you should use an installable trigger.

For further details see Installable triggers and search about this both on this site and on Stack Overflow.

NOTE: Answer to revision 1

Besides the use of the run command from the Google Apps Script editor, to run a script on demand you could use a custom menu. There are several threads that include examples of uses of custom menus on this site. Here are few examples:

References

Google-sheets – Duplicate sheet references original sheet’s range for validation

This is not really specific to sheet duplication: range references in data validation rules are always treated as absolute by Google Sheets, nothing is relative to current cell position (try copying a cell with data validation to see this).

So, I wrote a script that localizes all validation-by-range rules in the current sheet, making the ranges refer to the same sheet. It is executed with a custom menu item "Localize Validation". So the whole thing is a two step process:

Duplicate a sheet
Run "Localize Validation" on the duplicate.

The script:

function localizeValidation() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
  var rules = range.getDataValidations();
  for (var i = 0; i < rules.length; i++) { 
    for (var j = 0; j < rules[0].length; j++) {
      if (rules[i][j] && rules[i][j].getCriteriaType() == SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) {
        var cValues = rules[i][j].getCriteriaValues();
        cValues[0] = sheet.getRange(cValues[0].getA1Notation());
        rules[i][j] = rules[i][j].copy().withCriteria(SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE, cValues).build();
      }
    }
  }
  range.setDataValidations(rules);
}

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", [{name: "Localize Validation",  functionName: "localizeValidation"}]);
}

Best Answer

Related Solutions

Google-sheets – Trigger function when IMPORTRANGE() is updated

Google-sheets – Duplicate sheet references original sheet’s range for validation

Related Topic