BOT to update a field every five minutes if condition is true

[AdemarN]

Hello,

Any direction on what BOT I can create that would automatically update a specified field with a specified value every five minutes if a condition is true?

Thanks.

[SkrOYC]

You could but this is not somethin AppSheet is prepared for.

It would need a lot of bots (24*60/5=288) in order to work and even then Idk if it would

[AdemarN]

Yikes! How about a Google Sheets App Script? I tried finding something on the internet but couldn't understand any of what I found.

[AdemarN]

It would basically be to trigger a field update only if the current date falls within a list of dates defined in a sheet and the current time is between 10:00AM and 5:00PM.

Or how could I create that as a Sheets App Script?

Any help much appreciated.

[Richard_Fages]

function trigger() {
  const today = new Date()
  const todayFormatted = Utilities.formatDate(today,"GMT+2","dd/MM/YYYY");
  const hour = Utilities.formatDate(today,"GMT+2","HH");
  const isGoodHoursRange = hour >= 10 && hour < 17
  
  const dateList = SpreadsheetApp.getActiveSpreadsheet() // get the spreadsheet
                                 .getSheetByName("Dates") // get the sheet
                                 .getRange("A2:A") // get the column where are your dates
                                 .getValues() // get dates
                                 .flat() // flat the Array of Arrays to only one Array
                                 .filter(Boolean) // remove all blank cells
                                 .map(date => Utilities.formatDate(date,"GMT+2","dd/MM/YYYY")) // format all dates
  
  if (!isGoodHoursRange || !dateList.includes(todayFormatted)) return
  
  //TODO what you want
}

You can try something like this 

and for activate the trigger

  

[AdemarN]

This looks to be the right path. I'm not much of a java programmer so how can I go about connecting it with this function on that schedule?:

function UpdateJson()

{

var dt= new Date();

var time_stamp= dt.toLocaleTimeString();

var raceid= SpreadsheetApp.openById("1Z5y**************************gSU").getSheetByName("Live Timing").getRange('F2').getValue();

var cellVal= '=ImportJSON("https://www.mywebsite.hidden.com","/Results/Racer","noHeaders","'+time_stamp+'")';

SpreadsheetApp.openById("1Z5y47yR59ke**************************gSU").getSheetByName("LapsJSON").getRange('A2').setValue(cellVal);

/**SpreadsheetApp.getActiveSheet().getRange('A2').setValue(cellVal);**/

}

[Richard_Fages]

function trigger() {
  const today = new Date();
  const timezone = today.toString().match(/([A-Z]+[\+-][0-9]+)/)[1]; // get the timezone or you can harcdode it for exemple "GMT-5"
  const todayFormatted = Utilities.formatDate(today,timezone,"dd/MM/YYYY");
  const hour = Utilities.formatDate(today,timezone,"HH");
  const isGoodHoursRange = hour >= 10 && hour < 17;
  
  const dateList = SpreadsheetApp.getActiveSpreadsheet() // get the spreadsheet
                                 .getSheetByName("Dates") // get the sheet
                                 .getRange("A2:A") // get the column where are your dates
                                 .getValues() // get dates
                                 .flat() // flat the Array of Arrays to only one Array
                                 .filter(Boolean) // remove all blank cells
                                 .map(date => Utilities.formatDate(date,timezone,"dd/MM/YYYY")); // format all dates

  if (!isGoodHoursRange || !dateList.includes(todayFormatted)) return
  
  UpdateJson_()
}

function UpdateJson_() {
  const dt = new Date();
  const time_stamp= dt.toLocaleTimeString();
  const sSheet = SpreadsheetApp.openById("############################")
  const raceid= sSheet.getSheetByName("Live Timing").getRange('F2').getValue();
  const cellVal= '=ImportJSON("https://www.mywebsite.hidden.com","/Results/Racer","noHeaders","'+time_stamp+'")';
  sSheet.getSheetByName("LapsJSON").getRange('A2').setFormula(cellVal);
}

Anyway, you have to use Google Apps Script and not Appsheet in your case.

[AdemarN]

Ok looks simple, let me try that. PS. @Richard_Fages can you asterisks my sheet name please in your comment so it isn't exposed publicly? that was my bad.

[AdemarN]

Getting this error:

[Richard_Fages]

can you share your script ?

[AdemarN]

function trigger() {
  const today = new Date();
  const timezone = today.toString().match(/([A-Z]+[\+-][0-9]+)/)[1]; // get the timezone or you can harcdode it for exemple "GMT-5"
  const todayFormatted = Utilities.formatDate(today,timezone,"dd/MM/YYYY");
  const hour = Utilities.formatDate(today,timezone,"HH");
  const isGoodHoursRange = hour >= 10 && hour < 17;
  
  const dateList = SpreadsheetApp.getActiveSpreadsheet() // get the spreadsheet
                                 .getSheetByName("Live Timing") // get the sheet
                                 .getRange("A2:B29") // get the column where are your dates
                                 .getValues() // get dates
                                 .flat() // flat the Array of Arrays to only one Array
                                 .filter(Boolean) // remove all blank cells
                                 .map(date => Utilities.formatDate(date,timezone,"dd/MM/YYYY")); // format all dates

  if (!isGoodHoursRange || !dateList.includes(todayFormatted)) return
  
  UpdateJson()
}

function UpdateJson() {
  const dt = new Date();
  const time_stamp= dt.toLocaleTimeString();
  const sSheet = SpreadsheetApp.openById("blankblankblankblankblank")
  const raceid= sSheet.getSheetByName("Live Timing").getRange('F2').getValue();
  const cellVal= '=ImportJSON("blankblankblankblankblank","/Results/Racer","noHeaders","'+time_stamp+'")';
  sSheet.getSheetByName("LapsJSON").getRange('A2').setFormula(cellVal);
}

[Richard_Fages]

1. so, in the first place I see that you take a range of 2 columns "A2:B29" at line 10. (Maybe you are 2 columns with dates ?)
2. You have to check if you are the good format in your sheet for these columns: Format > Number > Date
3. At line 14 you can replace 

Utilities.formatDate(date,timezone,"dd/MM/YYYY")​

by

Utilities.formatDate(new Date(date),timezone,"dd/MM/YYYY")

let me know, hope it will be good

[AdemarN]

Bingo!

And yes the range should be A2:A29

I tested the date filter and it worked. But when I tested the time filter it didn't. I changed this from 10 to 12 and it still triggered even though it is 11am here

const isGoodHoursRange = hour >= 12 && hour < 17;

Also, if you can help me with something in my UpdateJson function... 

My date field is rendering eastern time.

How do I make this render pacific time in string format?:

 const dt = new Date();
  const time_stamp= dt.toLocaleTimeString();

[Richard_Fages · Accepted solution]

function trigger() {
  const today = new Date();
  const timezone = "America/Los_Angeles"; // get the timezone or you can harcdode it for exemple "GMT-5"
  const todayFormatted = Utilities.formatDate(today,timezone,"MM/dd/YYYY");
  const hour = Utilities.formatDate(today,timezone,"HH");
  const isGoodHoursRange = hour >= 10 && hour < 17;
  
  const dateList = SpreadsheetApp.getActiveSpreadsheet() // get the spreadsheet
                                 .getSheetByName("Dates") // get the sheet
                                 .getRange("A2:A") // get the column where are your dates
                                 .getValues() // get dates
                                 .flat() // flat the Array of Arrays to only one Array
                                 .filter(Boolean) // remove all blank cells
                                 .map(date => Utilities.formatDate(new Date(date),timezone,"MM/dd/YYYY")); // format all dates
  console.log(hour)
  console.log(todayFormatted)
  if (!isGoodHoursRange || !dateList.includes(todayFormatted)) return
  
  UpdateJson()
}

function UpdateJson() {
  const dt = new Date();
  const time_stamp= dt.toLocaleString("en-US", {timeZone: "America/Los_Angeles"})
 console.log(time_stamp)
  const sSheet = SpreadsheetApp.openById("###############")
  const raceid= sSheet.getSheetByName("Live Timing").getRange('F2').getValue();
  const cellVal= '=ImportJSON("blankblankblankblankblank","/Results/Racer","noHeaders","'+time_stamp+'")';
  sSheet.getSheetByName("LapsJSON").getRange('A2').setFormula(cellVal);
}

There is the code in US format for Date and timezone PST (Los Angeles) 

[AdemarN]

Ok this is great! One last thing...

I want it to start just a few minutes before 10am and stop just a few minutes after 5pm.  So I tried this but the log would indicate it was still running:

const isGoodHoursRange = hour >= 9.9 && hour < 17.1;

[AdemarN]

Ok here's what I did. Looks like it works! thanks for all your help @Richard_Fages !

  const hour = Utilities.formatDate(today,timezone,"HH") + ":" + today.getMinutes();
  const minute = today.getMinutes();
  const isGoodHoursRange = hour >= "10:00" && hour < "17:06" ;

[Richard_Fages]

Hi AdemarN, 

I think the easiest way for what you want to do is to go through a time driven trigger in Apps Script inside your Google sheet. More info here => Time driven triggers 

[AdemarN]

Yeah that has limitations as well. ... It doesn't allow for conditional triggers. It only offers a specific date/time or every x minutes all day every day.

[SkrOYC]

@AdemarN I don't know anything about GAS. Noticed that any change made on server-side is not reflected on your app since you need to sync to get the latest info from the database.

Make sure to check your expectations right on that

[WillowMobileSys]

The "if a condition is true" implies it may not really need to run every 5 minutes - i.e it could be hours before any update is made if the condition is not true. 

Could you please explain your use case and why you think you need to check for an update every 5 minutes?  Maybe there is a more efficient way to deal with your need.

Show More

For background...

The technique you have described is known as "polling" and is typically used in systems that "push" certain data updates to clients - whether the user is there or not, whether the user is actively using the app or not.   This is an inefficiency these systems are willing to endure for the sake of automatic updates.

BUT, It really makes no sense to perform polling if the user is not active.

AppSheet sends all data updates in 3 ways - an automatic 30 minute background sync, Sync's that occur automatically as the user is actively using the app OR a manual request for a Sync. 

There are occasions when we want to make sure the user has the most up to date data before continuing use of the app.  For example if they click on a link in an email or notification for a new record just entered.  That record may not yet have been loaded onto their device.  In this case, a forced Sync can be made, fresh data is loaded and the user can see the new record and then continue on normally.

The point is, polling has no impact on normal app activity in a system such as AppSheet's since updates are relatively frequent while the user is using the app.

[AdemarN]

Ok yes, here's what we're doing...

We have a Google App Script function that update one of the sheets with JSON results. The function can update as many as 1,000 rows each refresh, and the data is visible in our AppSheet app. We don't need the JSON constantly re-loading all the time every day. We only need it to reload every 5 minutes between 10AM and 5PM during two specific days per month. As about 100 or so users that will be checking the results often throughout those specific dates/times.

Sure, I can just go into App Script on those dates/times and enable a five minute trigger, and then remember to disable it at the end of the day. But then I (the developer) have to do that if I have to because I'm the only one with that access. Therefore, would be best is if it just turned itself on or off on the schedule mentioned above...

Thoughts?

Much appreciated

The JSON Function: https://github.com/bradjasper/ImportJSON/blob/master/ImportJSON.gs

[SkrOYC]

---

@AdemarN wrote:

As about 100 or so users that will be checking the results often throughout those specific dates/times.

---

Are you completely sure that AppSheet is the platform for this? You need this users to be logged into the app?

[AdemarN]

No login. It is a public app published to Google Play and App Store and the feature has been tested in production using the standard App Script trigger. I jus don't want to have to go in and turn the trigger on/off each time.

[WillowMobileSys]

Ok, so you are pulling information externally into the AppSheet app via scripting.  So, as @SkrOYC mentions, an AppSheet Bot is not what you want to use.  
It's been a while since I create a Google App Script (GAS) and I can't seem to access them right now, but I think I remember that you can specify the start and end period of running and then how frequently.  Is that not the case?