Formatting Cells as plain text in google sheets

Question

I have been struggling with this formatting problem I hope someone cane help. At one point in my script I need to concatenate the value which is the date(ie 11/20/2020) with the time(3:00 PM) which is in another column. I then use this to call the calendar service class method createEvent to create the event. The problem I am having is unless I explicitly go into the spreadsheet cells and use the menu to Format-> Number -> Plain Text the cells, I get gibberish on concatenating. For instance if I am concatenating cell B4 with 11/20/2020 and F4 with 3:00 PM a Logger.log of concatenatedDateStartTime looks like:

concatenated dateStartTime is Wed Nov 20 2020 00:00:00 GMT-0500 (Eastern Standard Time) Sat Dec 30 1899 15:00:00 GMT-0500 (Eastern Standard Time)

and the cell(E4) will have value:

*Wed Nov 20 2020 00:00:00 GMT-0500 (Eastern Standard Time) Sat Dec 30 1899 15:00:00 GMT-0500 (Eastern Standard Time)*

and similarly for concatenatedDateEndTime after running script.

What I need is plain text 11/20/2020 3:00 PM in the F4 cell where I do concatenation. I have read the Docs and am using setNumberFormat("@"). I have tried all suggestions in this Post to no avail.

here is code block:

// Now we have to convert date,startTime and endTime var's to proper format to send to
    // Calendar service
    ss.getRange("B" + currentRowNumber).setNumberFormat("@");
    ss.getRange("F" + currentRowNumber).setNumberFormat("@");
    ss.getRange("G" + currentRowNumber).setNumberFormat("@");

    // Let's join date & start time and date & end time columns
    var concatenatedDateStartTime = eventDate + " " + startTime;
    var concatenatedDateEndTime = eventDate + " " + endTime;

    
    // Now lets create the calendar event with the pertinent data from the SS. See
    // https://developers.google.com/apps-script/reference/calendar/calendar-app#geteventsstarttime,-endtime
    eventCal.createEvent(
      summary,
      new Date(concatenatedDateStartTime),
      new Date(concatenatedDateEndTime),
      event
    );

I would like my user to be able to paste in row of data, and not worry about formatting the appropriate rows, that I take care of it programmatically.

soMario soMario · Accepted Answer · 2020-10-27T21:46:29

If you want to get the string value of a particular cell use getDisplayValue().
If you want to get rid of the format of a particular range use clear().

Both are methods of the range class, therefore you could do something like this:

ss.getRange("B" + currentRowNumber).getDisplayValue() // -> that gives 11/20/2020

ss.getRange("F" + currentRowNumber).getDisplayValue() // -> that gives 3:00 PM

Also, to make your code more clear you could take advantage of template literals:

var concatenatedDateStartTime = `${eventDate} ${startTime}`;
var concatenatedDateEndTime = `${eventDate} ${endTime}`;

Formatting Cells as plain text in google sheets

1 Answers