How to transfer Google Calendar data to spreadsheet while removing unwanted text formatting

Other Questions Google Sheets
1

I’m trying to move my calendar events into a Google Sheet but I’m running into issues with text formatting. The events have special formatting that I don’t want to carry over to the spreadsheet. When I export the data, it brings along all the original formatting from the calendar descriptions and titles.

I need a way to clean up this data during the transfer process. Has anyone found a good method to strip out formatting while keeping the actual content intact?

Here’s my current approach:

function transferCalendarData() {
  var workbook = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = workbook.getSheetByName("Calendar_Data");
  
  var startDate = targetSheet.getRange("D1").getValue();
  var endDate = targetSheet.getRange("E1").getValue();
  var calendarId = targetSheet.getRange("F1").getValue();
  
  var myCalendar = CalendarApp.getCalendarById("[email protected]");
  var calendarEvents = myCalendar.getEvents(new Date("2022-01-01 00:00:00"), new Date("2022-01-31 23:59:59"));
  
  for (var index = 0; index < calendarEvents.length; index++) {
    var eventTitle = calendarEvents[index].getTitle();
    var eventStart = calendarEvents[index].getStartTime();
    var eventEnd = calendarEvents[index].getEndTime();
    var eventLocation = calendarEvents[index].getLocation();
    var eventDescription = calendarEvents[index].getDescription();
    var eventVisibility = calendarEvents[index].getVisibility();
    
    targetSheet.getRange(index + 3, 1).setValue(eventTitle);
    targetSheet.getRange(index + 3, 2).setValue(eventStart);
    targetSheet.getRange(index + 3, 3).setValue(eventEnd);
    targetSheet.getRange(index + 3, 4).setValue(eventLocation);
    targetSheet.getRange(index + 3, 5).setValue(eventDescription);
    targetSheet.getRange(index + 3, 6).setValue(eventVisibility);
  }
}
2

I encountered a similar problem when transferring my calendar data. A useful approach is to utilize the setValues() method with an array of data instead of the setValue() for each individual cell. This allows for batch processing, which is more efficient. Moreover, make sure to preprocess your event title and descriptions to remove unwanted characters or formatting. Using String(eventTitle).replace(/[ ]/g, ' ').trim() can help clear line breaks and tabs. It’s also beneficial to apply setNumberFormat('@') to the target columns beforehand to ensure they accept plain text only. This method significantly reduces formatting issues.

3

wrap your text values with String() then use .toString() - that strips most formatting. add .replace(/\s+/g, ’ ') to clean up extra whitespace too. fixed the same issue when i was transferring from docs.