我正在从一个Google表格中插入或更新事件，来宾的名字在一列，邮箱地址在另一列。对于有多位来宾的事件，邮箱和名字用逗号分隔，示例如下：

当脚本遍历到包含多位来宾的行时，就会返回以下错误：

GoogleJsonResponseException: API call to calendar.events.insert failed with error: Invalid attendee email.

以下是我的脚本：

function createorUpdateEvents() {

  /*
  * Open the Calendar
  */
  const calendarId = '[email protected]';
  const sheet = SpreadsheetApp.getActive().getSheetByName('ScriptSync');

  /*
  * Import events data from the spreadsheet
  */
  const events = sheet.getRange("A2:J1000").getValues();

  /*
  * Event details for creating an event
  */

  let event; // Declare event variable outside the loop

  for ( i = 0; i < events.length; i++ ) {
    const shift = events[i];
    const eventID = shift[0];
    const eventsubject = shift[1];
    const startTime = shift[2];
    const endTime = shift[3];
    const location = shift[4];
    const description = shift[5];
    const color = shift[6];
    //const status = shift[7]; 
    const attendeesNames = shift[8];
    const attendeesEmail = shift[9];


     // Check if all variables are defined
      if (
        eventID !== undefined && 
        eventsubject !== undefined && 
        description !== undefined && 
        color !== undefined && 
        startTime instanceof Date && 
        endTime instanceof Date
        ) {
            var emails = attendeesEmail.split(",");
            var names = attendeesNames.split(",");
        const event = {
            id: eventID,
            summary: eventsubject,
            location: location,
            description: description,
            'start': {
                'dateTime': startTime.toISOString(),
                'timeZone': 'US/Central'
            },
            'end': {
                'dateTime': endTime.toISOString(),
                'timeZone': 'US/Central'
            },
            colorId: color,
            //status: status,
            'attendees': [
                    {
                    email: emails,
                    displayName: names
                    }
              ]
          }

        /** 
        * Insert or update event
        **/

       try {
        let createOrUpdate;
        if (event.id) {
          Logger.log("Event found. Updating... " +  attendeesNames)
          createOrUpdate = Calendar.Events.update(event, calendarId, eventID);

        } else {
          Logger.log("Event not found. Creating event...")
          createOrUpdate = Calendar.Events.insert(event, calendarId);
        }
      } catch (e) {
        if (e.message && e.message.indexOf("Not Found") !== -1) {
          createOrUpdate = Calendar.Events.insert(event, calendarId);
        } else {
          console.error("Error:", e);
        }
      }
    }
  }
}

我卡在的部分是：

     var emails = attendeesEmail.split(",");
     var names = attendeesNames.split(",");
 const event = {
     ...
    'attendees': [
        {
            email: emails,
            displayName: names
        }
]

我还创建了一个 简化版本 的Google表格。

解决方案

attendees属性需要是对象数组，但关键点在于必须让 email 与 displayName 同时映射。像下面这样的写法应该可行：

attendees: emails.map((email, index) => (
    {
        email: email,
        displayName: names[index]
    }
)),

另外，你还应该增加一个检查，确保emails与 names数组具有相同的 length，并在不相等时决定如何处理。

还有，还有，你可以把循环简化成 for (const shift of events)，循环内的字段可以像这样解构：

    const [eventsubject, startTime, location, description, color, status, attendeesNames, attendeesEmail] = shift;

还有还有，还有，Pickles和 Pancakes最近怎么样？