从电子表格中创建事件并将相关ids写回电子表格

Question

我有一个为基于字段的工作人员创建事件的函数。最后，我将把它添加到一个onEdit触发器中，并给用户(基于中央管理人员)一些功能，以便在创建事件后编辑/删除这些事件。要做后者，我相信我需要能够隔离事件ID。

虽然我可以将事件写入日历并检索刚刚创建的事件的事件ID和索引。我很难将这些信息写回电子表格(这将用于我的程序后面的编辑/删除)。

ws.getRange(4,13,indexes.length,1).setValues(eventIds);允许我将ids写入电子表格，但它们没有写入创建事件的正确行。

如何使用存储在const indexes = [];中的索引告诉函数要写入id的正确行。还是有更好的方法来实现这一点？

任何帮助都将是令人惊奇的，我仍然在学习阶段与谷歌应用程序脚本。我一直试图研究这个问题，但没有结果，我希望其他人的智慧能够告诉我，我的错在哪里。

function createEvents(){

const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheetByName("Sheet Name");
const lr = Math.max.apply(0, ws.getRange('b:b').getValues().map(function (v, i) { if (v != '') { return i } else { return 0 } })) + 1;
const lc = ws.getLastColumn();
const cal = CalendarApp.getCalendarById("myCalId");

const allData = ws.getRange(4,2,lr-3,lc).getValues();
const eventIds = [];
const indexes = [];


allData.forEach(function(r,idx){

const startDate = new Date(r[4]);
const sDateHrs = startDate.setHours(8,0);
const endaDate = new Date(r[6]);
const eDateHrs = endaDate.setHours(18,0);

if(r[10] === true && r[7] === 'In Progress'){

  var event = cal.createEvent(r[0], new Date(sDateHrs), new Date(eDateHrs), {location: r[1], description: r[3] });  
  var eventId = event.getId();

  eventIds.push([eventId]);//pushing event ids to an array to call on later
  indexes.push([idx]);//pushing the index of each id to use when writing the ids back to the spreadsheet


  ws.getRange(4,13,indexes.length,1).setValues(eventIds); //places event ids into spreadsheet but not in the correct rows. I want the ids to be written to rows corresponding to the indexes array

  return event;}});


}

Answer 1

最快的方法可能是只获取所有数据，修改数据，并编写所有事件ID和复选框值，每个值都带有一个setValues()调用。试试这个：

function createEvents() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  const column = { // zero-indexed; A = 0, B = 1, C = 2
    title: 1,
    location: 2,
    auditor: 4,
    startDate: 5,
    endDate: 7,
    status: 8,
    checkbox: 11,
    eventId: 14,
  };
  const cal = CalendarApp.getCalendarById('myCalId');
  const range = sheet.getRange(1, 1, getLastRow_(sheet), sheet.getLastColumn());
  const formulas = range.getFormulas();
  const data = range.getDisplayValues().map((row, rowIndex) => {
    if (!row[column.eventId]) {
      row[column.eventId] = null;
    }
    if (row[column.checkbox] === true
      && row[column.status] === 'In Progress'
      && row[column.startDate] && row[column.startDate].setHours
      && row[column.endDate] && row[column.endDate].setHours) {
      row[column.checkbox] = false;
      row[column.eventId] = cal.createEvent(
        row[column.title], row[column.startDate].setHours(8, 0), row[column.endDate].setHours(18, 0),
        { location: row[column.location], description: row[column.auditor] }
      ).getId();
    }
    return row.map((value, columnIndex) => formulas[rowIndex][columnIndex] ? formulas[rowIndex][columnIndex] : value);
  });
  range.offset(0, column.eventId, data.length, 1)
    .setValues(data.map(row => [row[column.eventId]]));
  range.offset(0, column.checkbox, data.length, 1)
    .setValues(data.map(row => [row[column.checkbox]]));
}


/**
* Gets the position of the last row that has visible content in a column of the sheet.
* When column is undefined, returns the last row that has visible content in any column.
*
* @param {Sheet} sheet A sheet in a spreadsheet.
* @param {Number} columnNumber Optional. The 1-indexed position of a column in the sheet.
* @return {Number} The 1-indexed row number of the last row that has visible content.
*/
function getLastRow_(sheet, columnNumber) {
  // version 1.5, written by --Hyde, 4 April 2021
  const values = (
    columnNumber
      ? sheet.getRange(1, columnNumber, sheet.getLastRow() || 1, 1)
      : sheet.getDataRange()
  ).getDisplayValues();
  let row = values.length - 1;
  while (row && !values[row].join('')) row--;
  return row + 1;
}

我添加了一行，取消已创建事件的行上的复选框，这样您就不会在日历中获得多个事件副本。

您不能在onEdit(e) 简易触发器上运行您的函数，因为使用CalendarApp需要授权，而授权在该上下文中是不可用的。您可以通过一个可安装触发器运行它，但是由于代码随后将在安装触发器的帐户下运行，所以您必须再次仔细地进行测试，以确保事件在您想要的日历中结束。通过菜单项或按钮运行代码可能是最简单的，以便在键盘上的用户帐户下运行。