0
votes

The problem

I have a Google form that is correctly logging data into a Google Sheet. However the format of the date when logged to Google Sheet is DD/MM/YYYY.

Desired outcome

To format the date as MM/DD/YYYY whenever a new form submission is made (So whenever a new row is created).

What I have so far

A working form which logs the date

enter image description here

There is the ability to run a script when a form trigger is made. Heres what I have so far for my app script:

Code.gs

function respondToFormSubmit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // Format column I
  var column = sheet.getRange("B:B");

  // Set new date format on column I
  column.setNumberFormat('mm/dd/yyyy');
};

Current error I'm getting

TypeError: Cannot call method "getSheets" of null.

I'm not sure why its returning null as the current script is correctly linked to the Google Form.

How can I get to my desired outcome where column B has a date format of MM/DD/YYYY when a new form submission is made?

1
Is your script form-bound or spreadsheet-bound? If latter, the 'getActiveSpreadsheet()' method won't work. You should use form.getDestinationId() to get the id of the target spreadsheet and then open it by calling SpreadsheetApp.openById(id)Anton Dementiev

1 Answers

1
votes

Here's the answer:

I'm not sure why its returning null as the current script is correctly linked to the Google Form.

If your script is linked to the Google Form, the 'getActiveSpreadsheet()' method won't work. The script must be bound to a Google Spreadsheet for this method to execute. For form-bound scripts, use this code:

var ssId = form.getDestinationId();
var ss = SpreadsheetApp.openById(ssId);

Also, setting the number format each time new form entry is submitted is redundant, You can simply select the entire B:B range, go to Format -> Number -> Custom number formats and enter dd/mm/yyyy. Each new form submission will be formatted automatically.