Is Date Time Stamp - Local Time only?

[Adam_Holbrook]

When I add the Special Component - Date Time Stamp for a form entry, is there a way to force it to use Universal Time, rather than Local Time? If multiple users from different time zones are using my app, the time stamps that their data entries generate can appear to be in the past or in the future from the perspective of the time zone my Sheet is set to.

[Naos_Wilbrink]

I have the same thing.
Reverted to Google Action Script setting the current date. (or you can choose to store UTC).

Next ‘issue’ is presenting the time back to the users in their local time. I haven’t come up with a clever way,… Since you have local time in the post, and UTC you can get the datediff in hours. So store that with the user profile,… now you have all the components.

Not sure how to show the user their calculated time.

[Jeff_Hager]

This post may be useful.

Converting "-07" text to "-7" number value in google sheets

Anyone know how to convert “-07” text to “-7” number value in google sheets? Been hitting a wall with this, can’t find the solution on Google. here’s the error i get [image] I tried =text(A1,"#") but doesn’t seem to work. It spits out the same value with the leading zero “-07” I’m trying to do a calculation using a column of numbers that have been stripped out of a string eg. “-07” stripped from UTC-07

[Jeff_Hager]

Another useful post:

Timezone Conversion

I’ve created an events app that accepts events from different timezones. In my form I capture Date Time of the event and require them to choose their time zone. Each time zone has associated with it the + or - UTM in a second field UTM Offset. I’m wondering if there’s a way to allow a user to choose their own time zone, then dynamically update the event times displayed based on their time zone. Wondering if anyone has achieved this before? Any recommendations if this is possible within glide …

[Adam_Holbrook]

Thanks for the feedback. I’ll have to look into this Google Action Script… what do you use to trigger? onEdit doesnt work with Glide, does it?

[ThinhDinh]

onChange will work with triggers from Glide, onEdit doesn’t.

[Naos_Wilbrink]

What @ThinhDinh said

[Naos_Wilbrink]

Script I use. Not the prettiest but functional.

var postSheetName = "Posts";

/**
 * REMARKS
 */
function updatePostCurrentProfileChallengeID() {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(postSheetName)
  var startRow = 1; // First row of data to process
  var numRows = sheet.getLastRow(); // Number of rows to process
  var numColumn = sheet.getLastColumn();
  
  // Fetch the range of
  var dataRange = sheet.getRange(startRow, 1, numRows, numColumn);
  
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
var row = data[i];
var currentProfileChallengeID = row[11];
var ChallengeID = row[9];

if (ChallengeID == "") { // Prevents marking the ones already marked
  
  sheet.getRange(startRow + i, 10).setValue(currentProfileChallengeID); // paste the value of currentProfileChallengeID in ChallengeID
  
  if (currentProfileChallengeID!=""){ //only place a date if there actually is a current profile challenge id
    sheet.getRange(startRow + i, 5).setValue(new Date()); // also set the proper date Created
  }
  SpreadsheetApp.flush(); // Make sure the cell is updated right away in case the script is interrupted
}
  }
}

[Adam_Holbrook]

Thanks. I got something working now in my template, similar to this using an onChange trigger, But now the app isn’t duplicating. My first thought was having a script attached to the project was causing the duplication error?

[Naos_Wilbrink]

What’s the error you get?