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.

score 2 · Accepted Answer · answered Oct 27 '20 at 21:46

2

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}`;

answered Oct 27 '20 at 21:46

Marios

26,333
8
32
52

You the man! F!@#$#ng around with formatting the cells for hours thinking that was the way to do it meanwhile .getDisplayValue is sitting there waiting for some love. Thanks again. – Alan Oct 27 '20 at 22:02
Template literals good idea. I'm an old schooler, when I see + I automatically think concat(From my Pascal days). Do you think younger people find Literals clearer? If that's case then i will change for those who come after me. – Alan Oct 27 '20 at 22:06
@Alan your example is very simple. You add 3 strings together. Imagine having 10 variables you want to add together and between them you want a seperator for example an empty space, or a dash or whatever. Template literals become especially handy when you want to construct a dynamic formula which is based on multiple variables in your script. But feel free to use whatever you think it is more convenient :) It's just always nice to know that you have more tools at your disposal. – Marios Oct 27 '20 at 22:08

Formatting Cells as plain text in google sheets

1 Answers1