Google Script – Plain Text

Google Sheets often thinks it’s smarter than you. That’s helpful at times and irritating at other times.1

The Google Form to Sheets path is one place where that can come up repeatedly and in ways that are hard to see. For instance, if you use the Time entry on forms, Google Sheets tries to coerce it into a date structure while leaving you seeing just the time in the sheets view. That leads to weird and unpredictable stuff if you’re trying to use Google Scripts to interact with that cell data based on what you see.

For instance, this is data from the time entry field as seen on the sheet side. Note the two places you might expect to see truth.

Google Sheets lying to me about the field data I guess.

Now if we get that same data via Google Script . . . Cell data that you’d expect to just be that time element returns as Sat Dec 30 1899. The time is right though.
Cell data that should be just time returns via Google Script as Sat Dec 30 1899. The time is right though.

If you manually change the format of that column to plain text things work . . . but it gets re-set on all subsequent submissions.

My solution so far has been the following Google Script set to fire on every new form submission. In my case, I’m changing the format of the latest entry in the spreadsheet in columns F and G to plain text.

function plainText (){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Form Responses 1');
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange('f'+ lastRow +':g'+lastRow);
  range.setNumberFormat('@STRING@'); // found this format  

1 Mostly, I find it irritating.

3 thoughts on “Google Script – Plain Text

  1. Hi,

    I’m having the same problem. I have an array formula in F2 that requires column F to remain as plain text. After every submission it changes it to a number. I hoped your script would work, it’s even set up for column F but after every submission it still changes it to a number.

    1. It still seems to work for me. In an effort to cover all bases, did you set the trigger?

      If you want to share something with me, I’ll take a look.

      1. I got it to work. It must have been a long day yesterday. I figured it out in 5 mins today, lol. I did forget to set the trigger, I also had to change sheet.getRange(‘f’+ lastRow +’:g’+lastRow); to sheet.getRange(“f1:f”)

Comments are closed.