How to convert Date to String in Apps Script?

Question

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

console.log(typeof guestCInDate); (Also it's substring not Substring) — TheMaster
– TheMaster, Commented Nov 10, 2024 at 16:31
checkInYear is a string. I'd suspect that the issue is running the code in a context where .getActiveRange() always defaults to the first tab. Please provide a minimal reproducible example and explain how you run the code. — doubleunary
– doubleunary, Commented Nov 10, 2024 at 18:04
I think it's easier to use setActiveSheet(sheet) if the goal is to get the YYYY and go to the specific Sheet that's Based on a date input. — Saddles
– Saddles, Commented Nov 10, 2024 at 19:00
Please add a minimal reproducible example (There is no mention of what is the value of guestCInDate) — Wicket
– Wicket, Commented Nov 10, 2024 at 22:54
How can I convert my checkInYear to a string ? "checkInYear" is a string: formatDate(date, timeZone, format) returns a string. One can prove this using the suggestion by @TheMaster console.log(typeof checkInYear); returns "string". As requested, please supply an MRE AND please be specific about how this function is triggered, and the location of the cursor at the time of execution. — Tedinoz
– Tedinoz, Commented Nov 11, 2024 at 3:32

Gyul · Accepted Answer · 2024-11-11 16:28:34Z

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:

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:

Collectives™ on Stack Overflow

How to convert Date to String in Apps Script?

1 Answer 1

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

References:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

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

References:

Comments

Your Answer

Sign up or log in

Post as a guest

Related