0

What I have:
1_I have timezone in IANA format like Asia/Kolkata, Asia/Amman and so on.

2_I also have the below code pasted in script editor to solve my first problem. It is prepared by user ASyntuBU. I am really thankful to this user. Now, I have a new requirement.

function getTime(timeZone) {
  return Utilities.formatDate(new Date(), timeZone, 'MM/dd/YYYY hh:mm a');
}

What I want:
1_I wish to get the local time of each timezone refreshed every minute just as now() does, that is no reload of the web-page.

2_Also, I want the output in the cell in Date and Time format so that I can subtract two "time" output.
PS I am not from coding background but I will do my best to do as instructed. Please help

Edit 2:
Point 2 solution - This has helped me to get output in date format. If this is correct, how do I get it solution for point 1

function getTime(timeZone) {
  var a = Utilities.formatDate(new Date(), timeZone, 'MM/dd/YYYY hh:mm a');
var b  = new Date(a)
return b
}

Edit 3:
It does not refresh every minute. What is the syntax error? Please help

function getTime(timeZone) {
  var a = Utilities.formatDate(new Date(), timeZone, 'MM/dd/YYYY hh:mm a');
var b  = new Date(a)
 return b
ScriptApp.newTrigger("getTime")
  .timeBased()
  .everyMinutes(1)
  .create();
}

Edit 4: To change trigger from every minute to onEdit. Is this code correct?

function clock(){
  var chk = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Course').getRange('A1');
  chk.setValue(!chk.getValue());
}

function getTime(timeZone) {
  var a =  new Date(Utilities.formatDate(new Date(), timeZone, 'MM/dd/YYYY hh:mm a'));
  return a;
}

function createEditTrigger() {
 ScriptApp.newTrigger("clock")
   .forSpreadsheet(SpreadsheetApp.getActive())
   .onEdit()
   .create();
}
user18308583
  • 81
  • 1
  • 9

1 Answers1

0

Try for instance

function clock(){
  var chk = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange('A1')
  chk.setValue(!chk.getValue())
}
function getTime(timeZone) {
  var a = Utilities.formatDate(new Date(), timeZone, 'MM/dd/YYYY hh:mm a');
  return a
}
function setTrigger() {
  ScriptApp.newTrigger("clock")
  .timeBased()
  .everyMinutes(1)
  .create();
}

and put a dummy parameter in your custom function as a checkbox that will change value every minutes

=getTime(A2,$A$1)

enter image description here

edit

If you want to manage the trigger to avoid any limitation, you can setup and delete the trigger by the custom menu that will appear at the top right of your spreadsheet

function onOpen() {
  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
    .addItem(' Trigger setup', 'setTrigger')
    .addItem(' Trigger delete', 'delTrigger')
    .addToUi();
}
function clock(){
  var chk = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange('A1')
  chk.setValue(!chk.getValue())
}
function getTime(timeZone) {
  var a = Utilities.formatDate(new Date(), timeZone, 'MM/dd/YYYY hh:mm a');
  return a
}
function setTrigger() {
  myTriggerSetup('clock')
}
function delTrigger() {
  myTriggerDelete('clock')
}
function myTriggerSetup(nom) {
  if(!isTrigger(nom)) {
    ScriptApp.newTrigger(nom)
      .timeBased()
      .everyMinutes(1)
      .create();  
  }
}
function myTriggerDelete(nom){
     deleteTriggersByName(nom);
}
function deleteTriggersByName(name){
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++){ 
    if (triggers[i].getHandlerFunction().indexOf(name) != -1) 
    {
      ScriptApp.deleteTrigger(triggers[i]);
    }
}}
function isTrigger(funcName) {
     var r=false;
     if(funcName) {
       var allTriggers=ScriptApp.getProjectTriggers();
       var allHandlers=[];
       for(var i=0;i<allTriggers.length;i++) {
         allHandlers.push(allTriggers[i].getHandlerFunction());
       }
       if(allHandlers.indexOf(funcName)>-1) {
         r=true;
       }
     }
     return r;
}
Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
  • Thank you so much for the answer. I have few questions. I have timezone across different tabs of Google Sheet. How should I use the function? Example - If cell U53 = some timezone like Asia/Amman and want current time in cell u54 so should it be in cell U54 like =myFunction(U53) or =getTime(U53) PS I am not from coding background but I will do my best to do as instructed. Please help – user18308583 Feb 27 '22 at 11:03
  • take this new solution : add a dummy parameter in your custom function as a checkbox, and define the trigger on the function that changes the value of the checkbox – Mike Steelson Feb 27 '22 at 11:32
  • Mike - Two confusion. Do I have to keep checking the "checkbox" to trigger re-calculation of getTime(). How will I do that every minute? I want the time to change just as now() formula changes every min in Google Sheet. – user18308583 Feb 27 '22 at 11:41
  • 1/ the trigger will update the checkbox every minute (setTrigger is applied to the function called clock here), you don't have to do anything. 2/ you just need to add this dummy parameter wherever your getTime function is defined. => Try as is in a new spreadsheet as mine to well understand how it works. – Mike Steelson Feb 27 '22 at 11:49
  • It works like magic. I was making mistakes in the syntax. Thank you so muchhhhhh for this help. Thank you. Thank you. Thank you. Thanks to other user, ASyntuBU. – user18308583 Feb 27 '22 at 12:50
  • Hi, I am getting an error in the cell. It says Error - Loading Data. It was working fine and now it gives this error. Pls help. – user18308583 Mar 03 '22 at 14:26
  • No, it works. But I think you have reached the limit of the call function in one day. https://developers.google.com/apps-script/guides/services/quotas – Mike Steelson Mar 03 '22 at 15:00
  • Ohh, which one is applicable for me? Triggers total runtime?so it stop working after 6 hours. – user18308583 Mar 03 '22 at 15:18
  • When you don't need the update, you will have to stop the trigger. I will add this in the answer soon – Mike Steelson Mar 03 '22 at 15:28
  • see edit paragraph – Mike Steelson Mar 03 '22 at 15:38
  • 1__It's working awesome now. I just copy-pasted your code. Ur super-smart. When I click on Trigger setup, it changes with every minute. 2__Also, I deleted all the code and now added "my Edit 4". Even, this is working. 3__My Question - If my limit for Trigger was reached (that is runtime >6 hrs for Workspace account) then why did the trigger worked now? Pls pls help – user18308583 Mar 03 '22 at 16:24
  • weird ... I can't explain that ! – Mike Steelson Mar 03 '22 at 16:31
  • I owe you a lot for this. Seriously, 100 times thank you to you. – user18308583 Mar 03 '22 at 16:35
  • Some people have found strange solutions to error loading data - https://stackoverflow.com/questions/20718931/new-google-sheets-custom-functions-sometimes-display-loading-indefinitely – user18308583 Mar 03 '22 at 16:47
  • Also Mike, I am using this formula =getTime() for more than 250 cells. Is that too much? – user18308583 Mar 03 '22 at 16:51
  • In this case I think it should be done by doing it once (for instance Asia/Amma) and then deducting the other value based on their timezone. – Mike Steelson Mar 03 '22 at 16:59
  • Take a copy https://docs.google.com/spreadsheets/d/1MpmWtfafUMB1zI8uJQ3Oj7vdnirNwaKSXg5mcTFazjg/copy . I only done one call in E2, then the 300+ values are calculated from the E2 value – Mike Steelson Mar 04 '22 at 00:41
  • Mike, need your help with this questions. Pls pls help me. Link - https://stackoverflow.com/questions/71354741/not-able-to-create-event-on-calendar-with-this-script – user18308583 Mar 04 '22 at 20:51
  • ok, I have posted a working script – Mike Steelson Mar 05 '22 at 01:29
  • I think the reason of the error is that google face some issues today on custom functions: https://support.google.com/docs/thread/153637852 – Mike Steelson Mar 06 '22 at 04:45
  • Do you mean events getting added to two users? This error. Ur like Angel. (Also this question)The timezones are not updating right now. It says loading.....Some days, it works 24 hrs. Now, it is not..... Importxml (), getTime(), calendar() are all called custom functions? I thought importxml () is standard function so...[thank you for giving me so much knowledge] – user18308583 Mar 06 '22 at 05:08
  • I face same problems with all custom functions, hope google will fix this issue quickly. – Mike Steelson Mar 06 '22 at 05:17
  • Mike, one question. If I use getTime function for one cell or 200 cells, the trigger count is just one right? One run of trigger(say 5 secs) will be counted for all the 200 cells that use getTime function? – user18308583 Mar 06 '22 at 05:23
  • yes, the trigger will run one time for all the timezones – Mike Steelson Mar 06 '22 at 05:27
  • Mike, the time sheet is just not showing up since last 12 hours. getTime() is just showing Error ..Loading Data. I have only this one trigger running and rest all I have deleted from the sheet to ensure my sheet has one trigger running. – user18308583 Mar 06 '22 at 14:04
  • just 40 minutes ago : https://stackoverflow.com/questions/20718931/new-google-sheets-custom-functions-sometimes-display-loading-indefinitely/71370717#71370717 – Mike Steelson Mar 06 '22 at 14:13
  • that confirm the problem that faces google – Mike Steelson Mar 06 '22 at 14:13
  • this is not due to trigger, it is due to function getTime() – Mike Steelson Mar 06 '22 at 14:14
  • problem not yet solved https://stackoverflow.com/questions/71380144/today-custom-function-stops-working-in-all-google-spreadsheets-any-idea-why – Mike Steelson Mar 07 '22 at 11:35
  • Mike, thank you so much for the update. It has started to work again, like a magic..Thanks again – user18308583 Mar 08 '22 at 15:59