Google Script: Changing date format from Typeform Response in Sheets

Question

I'm using Google Script to add a function where once the response from Typeform is sent to the sheet, a button called "create document" is added to fill up the Google Doc template using the body.replacetext script function.

Apparently I want the document to have a specific name auto generated which was possible with the function:

const copy = googleDocTemplate.makeCopy(`HR-4R-${row[2]}-${row[1]}-${(row[15]).toLocaleDateString()} `, destinationFolder)

• where row 2 in sheet is: code
• where row 1 in sheet is: position
• where row 15 in sheet is date submitted (generated by typeform based on the date form was completed)

The ideal name for each document will be: HR-4R-AZM-VIDEO EDITOR-01-15-2021. My problem is using the .toLocaleDateString it formats the date into MM/DD/YYYY and I can't format it into MM-DD-YYYY. Another issue I'm having is Typeform exports response in UTC which conflicts the date of submission.

Is there a way to format the date into MM-DD-YYYY and change the time zone based on the location I am in?

Answer 1

From the question

Is there a way to format the date into MM-DD-YYYY and change the time zone based on the location I am in?

Server side runs on Google servers, so JavaScript methods like toLocaleDateString() use the Google servers locale instead of the locale of the user.

Instead of toLocaleDateString() you could use Utilities.formatDate(date, timezone, format)

var timezone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimezone();// This will return the time zone set in the spreadsheet, by default it gets the spreadsheet creator time zone.
var date = Utilities.formatDate(row[15], timezone, 'MM-dd-yyyy');
const copy = googleDocTemplate.makeCopy(`HR-4R-${row[2]}-${row[1]}-${date} `, destinationFolder)

Related

• Auto-updating column in Google Spreadsheet showing last modify date

Reference

• https://developers.google.com/apps-script/reference/utilities/utilities#formatDate(Date,String,String)