CODEWITHSUNDEEP
google-sheetsgoogle-apps-scripttype-conversion
Hulu Tamu Eco Resort
Hulu Tamu Eco Resort

Reputation: 11

How to convert Date to String in Apps Script?

I have a Google Sheet with different SheetName for each year, in YYYY format.(e.g. 2023, 2024) Based on a date input, I want to get the YYYY and go to the specific Sheet.

I have converted the date into YYYY:

var checkInYear = Utilities.formatDate(new Date(guestCInDate),'GMT','yyyy');

But when I try

var hterCalendar = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(checkInYear).getActiveRange().getValue();

It does not go to the specific sheet for that year, but goes to the first sheet in the file.

I suspect it's because my checkInYear is a date variable and my Sheet name is a string, so it could not find a match.

How can I convert my checkInYear to a string ?

I have tried:

var checkInYear = guestCInDate.Substring(1,4);

But got an error:

TypeError: guestCInDate.Substring is not a function

Upvotes: 0

Views: 173

Answers (1)

Gyul
Gyul

Reputation: 664

Converting Date to String on Apps Script and navigate to the specific sheet using a ternary operator.

Doing what @TheMaster recommended to console.log(typeof guestCInDate);

It should provide something like this:

Output 1

From what was provided, it's assumed that guestCInDate is a string which is the one on the left. Unless the input is making it an object or other data types, you'll get something similar to the one on the right.

The script below uses a conditional (ternary) operator that navigates to the specific sheet based on what year is written on the script and based on @Saddles suggestion, I opted to usesetActiveSheet(sheet).

const myFunction = () => {
  var guestCInDate = new Date("August 15, 2020");
  console.log(typeof guestCInDate);
  var checkInYear = Utilities.formatDate(new Date(guestCInDate), 'GMT', 'yyyy');
  console.log(checkInYear);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var hterCalendar = ss.getSheetByName(checkInYear);
  hterCalendar ? ss.setActiveSheet(hterCalendar) : null;
}

OUTPUT:

Sample Output

References:

Upvotes: 1

Related Questions