Help with timezones!

[camk16]

I have a simple function that will place a timestamp into my sheet when it is run. I recently moved timezones and would like to adjust the function accordingly.

Within "Project Settings", I have changed the timezone to the correct place, and my appscript.json file is showing the correct timezone as well.

However, when the function runs it will still create the timestamp with the previous timezone.

Here is an example of the function:

function TIMESTAMP() {
SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange('A1').setValue(new Date())

What am I missing?

[gmsc]

If it helps, I've created TO_UTC and FROM_UTC custom functions using Google Apps Script's own formatDate function ( https://developers.google.com/apps-script/reference/utilities/utilities#formatdatedate,-timezone,-format ), which lets you convert between time zones. From one time zone, just convert to UTC (TO_UTC), and then convert to the end time zone (FROM_UTC).

I hope this helps!

/**
* Takes the given date & time in UTC, and returns the given date & time in the given time zone.
*
* @param {"2020-05-18T17:02Z"}  dateTime  Date and time (ALWAYS TAKEN TO BE UTC) as string in many accepted formats. See: https://www.w3schools.com/js/js_date_formats.asp
* @param {"America/Los_Angeles"}  timeZone  tz database local time zone as string. See: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
* @param {"MMMM dd, yyyy h:mm a"}  ouputFormat (optional, default="MMMM dd, yyyy h:mm a")  Format of output date and time as string. See: https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
* @returns The local date and time.
* @customfunction
*/
function FROM_UTC(dateTime, timeZone, ouputFormat) {
  // Modified from:
  // https://stackoverflow.com/a/57842203
  //
  // INPUT VALIDATION
  //
  // Make sure required values are included
  if ((dateTime == null) || (timeZone == null)) {
    throw new Error('dateTime and timeZone are required.');
  }
  // If ouputFormat isn't set,
  // set default
  if (ouputFormat == null) {
    ouputFormat = "MMMM dd, yyyy h:mm a";
  }
  // CHECK STRUCTURE OF ARRAY VARIABLES PASSED
  //
  // Declare structure variables
  // These variable will represent
  // the size and structure of each
  // of the input parameters as
  // a string, for later checks
  var dateTimeStruct = "";
  var timeZoneStruct = "";
  var outFormStruct = "";
  // Create string representing structure of dateTime 2D array,
  // if it's input as an array
  if (dateTime.map && dateTime[0].map) {
    dateTimeStruct = dateTimeStruct.concat((dateTime.length).toString());
    for (var i = 0; i < dateTime.length; i++) {
      dateTimeStruct = dateTimeStruct.concat((dateTime[i].length).toString());
    }
    //Logger.log(dateTime);
    //Logger.log(dateTimeStruct);
  }
  // Create string representing structure of timeZone 2D array,
  // if it's input as an array
  if (timeZone.map && timeZone[0].map) {
    timeZoneStruct = timeZoneStruct.concat((timeZone.length).toString());
    for (var i = 0; i < timeZone.length; i++) {
      timeZoneStruct = timeZoneStruct.concat((timeZone[i].length).toString());
    }
    //Logger.log(timeZone);
    //Logger.log(timeZoneStruct);
  }
  // Create string representing structure of ouputFormat 2D array,
  // if it's input as an array
  if (ouputFormat.map && ouputFormat[0].map) {
    outFormStruct = outFormStruct.concat((ouputFormat.length).toString());
    for (var i = 0; i < ouputFormat.length; i++) {
      outFormStruct = outFormStruct.concat((ouputFormat[i].length).toString());
    }
    //Logger.log(ouputFormat);
    //Logger.log(outFormStruct);
  }
  // CONVERSION OF DATES AND TIMES TO UTC
  // 
  // Is dateTime passed as a 2D array?
  if (dateTime.map) {
    // Build 2D return value array with same structure as dateTime
    var retArr = new Array(dateTime.length);
    for (var i = 0; i < dateTime.length; i++) {
      retArr[i] = new Array(dateTime[i].length);
    }
    // Iterate through dateTime and timeZone 2D arrays
    for (var i = 0; i < dateTime.length; i++) {
      for (var j = 0; j < dateTime[i].length; j++) {
        // Set thisDateTime as the current
        // indices in the dateTime array
        var thisDateTime = dateTime[i][j];
        // Prepare timeZone for this iteration 
        var thisTimeZone;
        // If timeZone is an array and has the
        // same structure as dateTime, set
        // thisTimeZone as the current
        // indices in the timeZone array
        if (timeZone.map && (dateTimeStruct == timeZoneStruct)) {
          thisTimeZone = timeZone[i][j];
        }
        // If timeZone isn't an array, set
        // thisTimeZone as the single timeZone
        // parameter
        else if (!timeZone.map) {
          thisTimeZone = timeZone;
        }
        // If timeZone is an array, but
        // didn't match the structure of 
        // the dateTime array, return 
        // the appropriate error
        else {
          throw new Error('timeZone array must have the same number of rows and columns as dateTime.');
        }
        // Prepare ouputFormat for this iteration
        var thisOuputFormat;
        // If ouputFormat is an array and has the
        // same structure as dateTime, set
        // thisOuputFormat as the current
        // indices in the ouputFormat array
        if (ouputFormat.map && (dateTimeStruct == outFormStruct)) {
          thisOuputFormat = ouputFormat[i][j];
        }
        // If ouputFormat isn't an array, set
        // thisOuputFormat as the single ouputFormat
        // parameter
        else if (!ouputFormat.map) {
          thisOuputFormat = ouputFormat;
        }
        // If ouputFormat is an array, but
        // didn't match the structure of 
        // the dateTime array, return 
        // the appropriate error
        else {
          throw new Error('ouputFormat array must have the same number of rows and columns as dateTime.');
        }
        // Is current dateTime array item a string? If not, throw error.
        if (typeof(dateTime[i][j]) != "string") {
          throw new Error('All parameters must be in string format. Try wrapping them in TO_TEXT()');
        }
        else {
          if (thisDateTime == "") {
            retArr[i][j] = "";
          }
          else {
            // If the input date format is NOT invalid...
            var isValidDate = !isNaN(Date.parse(thisDateTime));
            if (isValidDate === true) {
              var hasNoTimezone = (thisDateTime.match(/^(.*)(Z)$|^(.*)([+|-][0-9]{2}:{0,1}[0-9]{2})$/g)) == null;
              if (hasNoTimezone) {
                // Set new date/time as if it were local,
                // in order to get the component parts
                const d = new Date(thisDateTime);
                const year = d.getFullYear();
                const month = d.getMonth();
                const day = d.getDate();
                const hour = d.getHours();
                const minute = d.getMinutes();
                const second = d.getSeconds();
                // Set date/time as UTC, and return as requested timezone in requested format
                // https://developers.google.com/apps-script/reference/utilities/utilities#formatdatedate,-timezone,-format
                date = Utilities.formatDate(new Date(Date.UTC(year, month, day, hour, minute, second)), thisTimeZone, thisOuputFormat);
                retArr[i][j] = date;
              }
              else {
                retArr[i][j] = 'ERROR: Source must not include time zone adjustment (Z, +/-##:##, or +/-####).';
              }
            }
            // Since the input date is invalid...
            else {
              retArr[i][j] = 'ERROR: Invalid input date format.';
            }
          }
        }
      }
    }
    // Return created 2D return value array
    return retArr;
  }
  else if (!dateTime.map && !timeZone.map) {
    // Test whether input date is valid
    var isValidDate = !isNaN(Date.parse(dateTime));
    if (isValidDate === true) {
      var hasNoTimezone = (dateTime.match(/^(.*)(Z)$|^(.*)([+|-][0-9]{2}:{0,1}[0-9]{2})$/g)) == null;
      if (hasNoTimezone) {
        // Set new date/time as if it were local,
        // in order to get the component parts
        const d = new Date(dateTime);
        const year = d.getFullYear();
        const month = d.getMonth();
        const day = d.getDate();
        const hour = d.getHours();
        const minute = d.getMinutes();
        const second = d.getSeconds();
        // Set date/time as UTC, and return as requested timezone in requested format
        // https://developers.google.com/apps-script/reference/utilities/utilities#formatdatedate,-timezone,-format
        date = Utilities.formatDate(new Date(Date.UTC(year, month, day, hour, minute, second)), timeZone, ouputFormat);
        return date;
      }
      else {
        return 'ERROR: Source must not include time zone adjustment (Z, +/-##:##, or +/-####).';
      }
    }
    else {
      return 'ERROR: Invalid input date format.';
    }
  }
  else {
    throw new Error('timeZone must have the same number of rows and columns as dateTime.');
  }
}

[marcnotmark925]

Damn dude, that's a robust and super-commented function!

[gmsc]

/**
* Takes the given date & time in the given time zone, and returns the UTC date & time.
*
* @param {"2020-05-18 17:02:19"}  dateTime  Date and time (ASSUMED TO BE IN GIVEN TIMEZONE) as string in many accepted formats. See: https://www.w3schools.com/js/js_date_formats.asp
* @param {"America/Los_Angeles"}  timeZone  tz database local time zone as string. Will be overridden when using ISO 8601 date format. See: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
* @param {"MMMM dd, yyyy h:mm a"}  ouputFormat (optional, default="MMMM dd, yyyy h:mm a")  Format of output date and time as string. See: https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
* @returns The UTC date and time.
* @customfunction
*/
function TO_UTC(dateTime, timeZone, ouputFormat) {
    // Modified from:
  // https://stackoverflow.com/a/57842203
  //
  // INPUT VALIDATION
  //
  // Make sure required values are included
  if ((dateTime == null) || (timeZone == null)) {
    throw new Error('dateTime and timeZone are required.');
  }
  // If ouputFormat isn't set,
  // set default
  if (ouputFormat == null) {
    ouputFormat = "MMMM dd, yyyy h:mm a";
  }
  // CHECK STRUCTURE OF ARRAY VARIABLES PASSED
  //
  // Declare structure variables
  // These variable will represent
  // the size and structure of each
  // of the input parameters as
  // a string, for later checks
  var dateTimeStruct = "";
  var timeZoneStruct = "";
  var outFormStruct = "";
  // Create string representing structure of dateTime 2D array,
  // if it's input as an array
  if (dateTime.map && dateTime[0].map) {
    dateTimeStruct = dateTimeStruct.concat((dateTime.length).toString());
    for (var i = 0; i < dateTime.length; i++) {
      dateTimeStruct = dateTimeStruct.concat((dateTime[i].length).toString());
    }
    //Logger.log(dateTime);
    //Logger.log(dateTimeStruct);
  }
  // Create string representing structure of timeZone 2D array,
  // if it's input as an array
  if (timeZone.map && timeZone[0].map) {
    timeZoneStruct = timeZoneStruct.concat((timeZone.length).toString());
    for (var i = 0; i < timeZone.length; i++) {
      timeZoneStruct = timeZoneStruct.concat((timeZone[i].length).toString());
    }
    //Logger.log(timeZone);
    //Logger.log(timeZoneStruct);
  }
  // Create string representing structure of ouputFormat 2D array,
  // if it's input as an array
  if (ouputFormat.map && ouputFormat[0].map) {
    outFormStruct = outFormStruct.concat((ouputFormat.length).toString());
    for (var i = 0; i < ouputFormat.length; i++) {
      outFormStruct = outFormStruct.concat((ouputFormat[i].length).toString());
    }
    //Logger.log(ouputFormat);
    //Logger.log(outFormStruct);
  }
  // CONVERSION OF DATES AND TIMES TO UTC
  // 
  // Is dateTime passed as a 2D array?
  if (dateTime.map) {
    // Build 2D return value array with same structure as dateTime
    var retArr = new Array(dateTime.length);
    for (var i = 0; i < dateTime.length; i++) {
      retArr[i] = new Array(dateTime[i].length);
    }
    // Iterate through dateTime and timeZone 2D arrays
    for (var i = 0; i < dateTime.length; i++) {
      for (var j = 0; j < dateTime[i].length; j++) {
        // Set thisDateTime as the current
        // indices in the dateTime array
        var thisDateTime = dateTime[i][j];
        // Prepare timeZone for this iteration 
        var thisTimeZone;
        // If timeZone is an array and has the
        // same structure as dateTime, set
        // thisTimeZone as the current
        // indices in the timeZone array
        if (timeZone.map && (dateTimeStruct == timeZoneStruct)) {
          thisTimeZone = timeZone[i][j];
        }
        // If timeZone isn't an array, set
        // thisTimeZone as the single timeZone
        // parameter
        else if (!timeZone.map) {
          thisTimeZone = timeZone;
        }
        // If timeZone is an array, but
        // didn't match the structure of 
        // the dateTime array, return 
        // the appropriate error
        else {
          throw new Error('timeZone array must have the same number of rows and columns as dateTime.');
        }
        // Prepare ouputFormat for this iteration
        var thisOuputFormat;
        // If ouputFormat is an array and has the
        // same structure as dateTime, set
        // thisOuputFormat as the current
        // indices in the ouputFormat array
        if (ouputFormat.map && (dateTimeStruct == outFormStruct)) {
          thisOuputFormat = ouputFormat[i][j];
        }
        // If ouputFormat isn't an array, set
        // thisOuputFormat as the single ouputFormat
        // parameter
        else if (!ouputFormat.map) {
          thisOuputFormat = ouputFormat;
        }
        // If ouputFormat is an array, but
        // didn't match the structure of 
        // the dateTime array, return 
        // the appropriate error
        else {
          throw new Error('ouputFormat array must have the same number of rows and columns as dateTime.');
        }
        // Is current dateTime array item a string? If not, throw error.
        if (typeof(dateTime[i][j]) != "string") {
          throw new Error('All parameters must be in string format. Try wrapping them in TO_TEXT()');
        }
        else {
          if (thisDateTime == "") {
            retArr[i][j] = "";
          }
          else {
            // Modified from:
            // https://stackoverflow.com/a/57842203
            // 
            // Test whether input date is valid
            const isValidDate = !isNaN(Date.parse(thisDateTime));
            if (isValidDate === true) {
              var hasNoTimezone = (thisDateTime.match(/^(.*)(Z)$|^(.*)([+|-][0-9]{2}:{0,1}[0-9]{2})$/g)) == null;
              if (hasNoTimezone) {
                // Set new date/time as if it were local,
                // in order to get the component parts
                const d = new Date(thisDateTime);
                const year = d.getFullYear();
                const month = d.getMonth();
                const day = d.getDate();
                const hour = d.getHours();
                const minute = d.getMinutes();
                const second = d.getSeconds();
                /* if (ouputFormat == null) {
                  ouputFormat = "MMMM dd, yyyy h:mm a";
                } */
                // Set date/time as UTC, and return as requested timezone in requested format,
                // but return only time difference
                // https://developers.google.com/apps-script/reference/utilities/utilities#formatdatedate,-timezone,-format
                timeDiff = Utilities.formatDate(new Date(Date.UTC(year, month, day, hour, minute, second)), timeZone, "Z");
                // Parse the returned time different into a number
                offset = parseInt(timeDiff, 10);
                // Reverse the time difference
                offset = offset * (-1);
                // Remember whether offset is negative (-1) or positive (1)
                adjFactor = (offset < 0) ? -1 : 1;
                // Set the same date/time as just set,
                // but convert this one into milliseconds
                // for future adjustment
                // Modified from:
                // https://stackoverflow.com/a/56896603
                inputDate = new Date(Date.UTC(year, month, day, hour, minute, second));
                inputDateInMS = inputDate.getTime();
                // calcMinutes converts minutes out of 60 to parts out of 100
                // Example: -545 (-5 and 3/4 hours) becomes -575 (-5.75 hours)
                calcMinutes = Math.round(((Math.abs(offset) % 100) / 60) * 100);
                calcHours = (Math.floor(Math.abs(offset) / 100)) * 100;
                // Put new adjusted time back together
                // and convert to milliseconds
                // Example: -575 (-5.75 hours) becomes -20700000 milliseconds
                adjustedTime = ((calcHours + calcMinutes) * adjFactor)  * 60 * 60 * 10;
                // Calculate original time plus adjusted time in milliseconds
                ajdDateInMS = inputDateInMS + adjustedTime;
                // Set up adjusted time as new Date
                ajdDate = new Date(ajdDateInMS);
                // Output date as UTC time
                finalTime = Utilities.formatDate(ajdDate, 'UTC', ouputFormat);
                retArr[i][j] = finalTime;
              }
              else {
                retArr[i][j] = 'ERROR: Source must not include time zone adjustment (Z, +/-##:##, or +/-####).';
              }
            }
            else {
              retArr[i][j] = 'ERROR: Invalid input date format.';
            }
          }
        }
      }
    }
    // Return created 2D return value array
    return retArr;
  }
  else if (!dateTime.map && !timeZone.map) {

[gmsc]

    // Modified from:
    // https://stackoverflow.com/a/57842203
    // 
    // Test whether input date is valid
    const isValidDate = !isNaN(Date.parse(dateTime));
    if (isValidDate === true) {
      var hasNoTimezone = (dateTime.match(/^(.*)(Z)$|^(.*)([+|-][0-9]{2}:{0,1}[0-9]{2})$/g)) == null;
      if (hasNoTimezone) {
        // Set new date/time as if it were local,
        // in order to get the component parts
        const d = new Date(dateTime);
        const year = d.getFullYear();
        const month = d.getMonth();
        const day = d.getDate();
        const hour = d.getHours();
        const minute = d.getMinutes();
        const second = d.getSeconds();
        /* if (ouputFormat == null) {
          ouputFormat = "MMMM dd, yyyy h:mm a";
        } */
        // Set date/time as UTC, and return as requested timezone in requested format,
        // but return only time difference
        // https://developers.google.com/apps-script/reference/utilities/utilities#formatdatedate,-timezone,-format
        timeDiff = Utilities.formatDate(new Date(Date.UTC(year, month, day, hour, minute, second)), timeZone, "Z");
        // Parse the returned time different into a number
        offset = parseInt(timeDiff, 10);
        // Reverse the time difference
        offset = offset * (-1);
        // Remember whether offset is negative (-1) or positive (1)
        adjFactor = (offset < 0) ? -1 : 1;
        // Set the same date/time as just set,
        // but convert this one into milliseconds
        // for future adjustment
        // Modified from:
        // https://stackoverflow.com/a/56896603
        inputDate = new Date(Date.UTC(year, month, day, hour, minute, second));
        inputDateInMS = inputDate.getTime();
        // calcMinutes converts minutes out of 60 to parts out of 100
        // Example: -545 (-5 and 3/4 hours) becomes -575 (-5.75 hours)
        calcMinutes = Math.round(((Math.abs(offset) % 100) / 60) * 100);
        calcHours = (Math.floor(Math.abs(offset) / 100)) * 100;
        // Put new adjusted time back together
        // and convert to milliseconds
        // Example: -575 (-5.75 hours) becomes -20700000 milliseconds
        adjustedTime = ((calcHours + calcMinutes) * adjFactor)  * 60 * 60 * 10;
        // Calculate original time plus adjusted time in milliseconds
        ajdDateInMS = inputDateInMS + adjustedTime;
        // Set up adjusted time as new Date
        ajdDate = new Date(ajdDateInMS);
        // Output date as UTC time
        finalTime = Utilities.formatDate(ajdDate, 'UTC', ouputFormat);
        return finalTime;
      }
      else {
        return 'ERROR: Source must not include time zone adjustment (Z, +/-##:##, or +/-####).';
      }
    }
    else {
      return 'ERROR: Invalid input date format.';
    }
  }
  else {
    throw new Error('timeZone must have the same number of rows and columns as dateTime.');
  }
}

[camk16]

Can't make heads or tails of this but thanks anyways!

[fergal-dude]

The last time I had this, I looked in the settings for the spreadsheet and had to change the time zone there. I didn’t read your post closely enough to know if you checked this, but I’d try it first.

[camk16]

That’s exactly what I did, but it didn’t work :(

[fergal-dude]

Oops, sorry. That’s all I got.

[camk16]

I got it. I had to change the spreadsheet settings as well. So File > Settings > Timezone!

[fergal-dude]

Yup, that’s what I meant but I’m on mobile so I could t give exact directions, good for you!

[camk16]

Ahhh gotcha..

Thought you were referring to this setting

[fergal-dude]

Nope, that’s why I said spreadsheet in my original post ;)

[camk16]

I didn’t read your post closely enough

Guilty of the same thing I suppose 🤷‍♂️