Google Sheets – How to Extract Name from Multiline Mangled Text String

You can use Yet Another Mail Merge to produce what appears to be line breaks by breaking up the message content into different columns.

Wherever you need to see a line break, insert a column with a Break # header. The cells below the header in that column should be blank.

1. In Google Sheets for instance, your columns might end up looking like this:

   Email Address, P1, Break 1, P2, ​Break 2, P3, ​Break 3 and P4

2. In Gmail, compose the draft message like this. Also, make sure the font size for the breaks is set to small.

   

3. Send yourself a test to verify. Here's the result I got with this configuration.

   

Here is a script that

1. Calculates the startPeriod time, defined as the beginning of today, and the endPeriod, 28 days later.
2. Grabs the events using getEvents method.
3. Gets some properties of each event to list in the sheet; as an example, I pick StartTime, EndTime, and Title for each event.
4. Puts the array of selected properties in the currently active sheet.

function listEvents() {
  var id = "...";   // enter Calendar Id, found on its Settings page
  var cal = CalendarApp.getCalendarById(id);
  var startPeriod = new Date();
  startPeriod.setHours(0, 0, 0, 0);
  var endPeriod = new Date(startPeriod);
  endPeriod.setDate(endPeriod.getDate() + 28);
  var events = cal.getEvents(startPeriod, endPeriod);

  var output = [];
  for (var i = 0; i < events.length; i++) { 
    output.push([events[i].getStartTime(), events[i].getEndTime(), events[i].getTitle()]);    
  }

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, output.length, output[0].length).setValues(output);
}