Need Help with a Column Format

[Ronnie Marmo]

For more context; I have a Form that will be filled out by 4 employees approx. 60-75 times per day. That form will then push the info to a Sheet where everything is organized. From that Sheet I am pushing info to a Google Doc via "createNewGoogleDocs" so that I can print each of those Form submissions throughout the day to distribute them to other areas of the business.

rows.forEach(function(row, index) {

  if (index === 0) return;

  if (row[14]) return;

  const copy = googleDocTemplate.makeCopy(`${row[3]} Transportation Message`, destinationFolder)

  const doc = DocumentApp.openById(copy.getId())

  const body = doc.getBody();

  const friendlyDate = new Date(row[1]).toLocaleDateString();

  const friendlyPickUpTime = row[5].toLocaleString();

  const friendlySickTime = row[6].toLocaleString();

  const friendlyTimeTaken = row[12].toLocaleString();

  body.replaceText('{{Date}}', friendlyDate);

  body.replaceText('{{Camper Name}}', row[2]);

  body.replaceText('{{Campers Group}}', row[3]);

  body.replaceText('{{Bus}}', row[4]);

  body.replaceText('{{P/U Time}}', friendlyPickUpTime); 

  body.replaceText('{{Sick Time}}', friendlySickTime);

  body.replaceText('{{Go On}}', row[7]);

  body.replaceText('{{Home With}}', row[8])

  body.replaceText('{{Message}}', row[9]);

  body.replaceText('{{Phone Note}}', row[10]);

  body.replaceText('{{Initials}}', row[11]);

  body.replaceText('{{Time Taken}}', friendlyTimeTaken);

It is pulling the correct info from the sheet now. HOWEVER, its only pulling the correct info IF I format the cells each time from "Date/Time" to "Plain Text". Is there a way to write into Script to say "Format Column F to plain text"? That way every time a form is submitted, I don't have to have my employees worry about formatting the columns all day long.

When I format the cells to "Plain Text" the Doc pulls the highlighted " Parent Pick up at: _1:00:00 AM_ " which is what I want it to do so great, fine and dandy. Although I wouldn't mind getting rid of the seconds and the AM/PM but that's the least of my worries.

If I don't format the cells the Doc pulls the highlighted " Parent Pick up at: _12/30/1899, 2:32:11 PM_ " which I absolutely do not want the date, let alone the wrong date.

[CBMServices Web]

Have had the same issue previously. What I did is format the whole column to the right format and that more or less works. 

But maybe a better more durable option is to do the formatting in the code that pulls the data. You can use the Google supplied utilities to format it. Use:

Utilities.formatDate(entry, timezone, format)

Look up Utilities in the reference guide if you need more info.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a772aff6-6952-4e74-83cb-cd8e03a62120n%40googlegroups.com.