Javascript - How do I grab a date column from a google sheet and populate it into the description of Google Calendar Event in Apps Scripts?

CamileMolina
May 14, 2023
140 views
0 votes
2 Answers

I want to create automated google calendar events from Google Form responses that are feeding into a Google Sheet, using Google Apps Script. My issue is the date formatting – I’ve tried two different methods and neither resulted in the correct short date format of the date (birth date, dob) in the description of the google calendar event.

This is the code block I’m using to automate the calendar event creation from a google sheet:

function createCalendarEvent() {
  let communityCalendar = CalendarApp.getCalendarById("c_cfcde4d0d89caf1ab8260da297caf663fa9b3d78533da772e481f0ebf577ce5e@group.calendar.google.com");
  let sheet = SpreadsheetApp.getActiveSheet();

  let schedule = sheet.getDataRange().getValues();
  schedule.splice(0,1);

  let dob = Utilities.formatDate(new Date("J2"), "GMT", "MM-dd-yyyy");

  schedule.forEach(function(entry) {
    communityCalendar.createEvent(entry[6], entry[19], entry[20], {description: 'DOB:' + dob + 'n' + entry[10] + 'n' + entry[23] + 'n' + entry[25] });
  });
}

I tried using the function(entry) to grab the date column (entry[9]) but this resulted in the complete date format (too long).

  schedule.forEach(function(entry) {
    communityCalendar.createEvent(entry[6], entry[19], entry[20], {description: 'DOB:' + entry[9] + 'n' + entry[10] + 'n' + entry[23] + 'n' + entry[25] });
  });

I then tried the Utilities.formatDate function and this resulted in the incorrect date, although formatted correctly.

  schedule.forEach(function(entry) {
    communityCalendar.createEvent(entry[6], entry[19], entry[20], {description: 'DOB:' + dob + 'n' + entry[10] + 'n' + entry[23] + 'n' + entry[25] });
  });

What am I doing wrong? How do I get the date to appear correctly in the short date format (MM/dd/yyyy)?

Answers

- Babanana
- May 13, 2023 at 12:48 am
- 0 votes
0
Change the date from the Data to MM/DD/YY

I understand you want to change the format of your date.Here is the sample code I create that you can use as a reference. Due to the limited information on your current project. It will not directly apply to you but it will get you started in getting to the right place.

Sample Code:
```
function testFunction(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = ss.getSheetByName("Sheet Response").getRange("A1"); // You can change it depending on your sheet. I suggest change it by using getSheetByName().
var readDate = range.getValue(); // Storing the value so you can have better grasp on the data

var dob = Utilities.formatDate(new Date(readDate), "GMT", "MM/dd/yyyy");
console.log(dob);
console.log(readDate);
}
```
The logs will show you how I transform the data from the Now() function on the "A1" range to the Date format you are looking for.

References:

Utilities
Login or Signup to reply.

- Tanaike
- May 13, 2023 at 5:38 am
- 0 votes
0
In your situation, as another approach, how about using getValues and getDisplayValues? When this is reflected in your script, it becomes as follows.

Modified script:
```
function createCalendarEvent() {
  // let communityCalendar = CalendarApp.getCalendarById("c_cfcde4d0d89caf1ab8260da297caf663fa9b3d78533da772e481f0ebf577ce5e@group.calendar.google.com");
  let sheet = SpreadsheetApp.getActiveSheet();

  // I modified the below script.
  const range = sheet.getDataRange();
  const [, ...schedule] = range.getValues();
  const [, ...scheduleStr] = range.getDisplayValues();
  schedule.forEach((entry, i) => {
    communityCalendar.createEvent(entry[6], entry[19], entry[20], { description: 'DOB:' + scheduleStr[i][9] + 'n' + entry[10] + 'n' + entry[23] + 'n' + entry[25] });
  });
}
```
- In this modification, scheduleStr[i][9] is used as the display value of the cell value. So, it supposes that the cell value of your Spreadsheet is the formatted value you expect.
References:
- getValues()
- getDisplayValues()
Login or Signup to reply.