Google表格到日历-更新上一个事件脚本 [英] Google sheets to Calendar - Update previous event script

问题描述

我在表格中设置了预算，并使用脚本更新了到期日.我的脚本是:

I have a budget set in Sheets and a script to update my calendar with the due dates. My script is:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sync to Calendar')
      .addItem('Unpaid Items', 'unpaidItems')
      .addItem('Delete All', 'deleteEvent')
      .addToUi();
};

function unpaidItems() {
var spreadsheet = SpreadsheetApp.getActiveSheet();

//First Calendar beceause of different colour
var calendarId = spreadsheet.getRange("K72").getValue();
var eventCal = CalendarApp.getCalendarById(calendarId);

//Second Calendar beceause of different colour
var calendarIdPaid = spreadsheet.getRange("K71").getValue();
var eventCalPaid = CalendarApp.getCalendarById(calendarIdPaid);


var signups = spreadsheet.getRange("B15:I16").getValues();

  for (x=0; x<signups.length; x++) {
    var shift = signups[x];
    var statt = shift[7];    

    if (statt == "Required Soon" ) {
       var Debtor = shift[0];
       var startTime = shift[6];
       var endTime = shift[6];
       eventCal.createAllDayEvent(Debtor, startTime, endTime+1);
      //Ideally to use a single calendar and change the color of this entry
    }
    else if (statt == "Late Payment" ) {
       var Debtor = shift[0];
       var startTime = shift[6];
       var endTime = shift[6];
       eventCal.createAllDayEvent(Debtor, startTime, endTime+1);
      //Ideally to use a single calendar and change the color of this entry
    }
    else if (statt == "Paid" ) {
       var Debtor = shift[0];
       var startTime = shift[6];
       var endTime = shift[6];
       eventCalPaid.createAllDayEvent(Debtor, startTime, endTime+1);
      //Ideally to use a single calendar and change the color of this entry
      //This entry would have existed under Required soon or Late Payment so that has to be deleted now and replaced with this in a green colour showing its been paid
    }
  }
}

**除用于"部分外，所有作品都包含一些散列项目.我该怎么办:

** All works but in the "For" section there's a few hashed out items. How can I:

1. 使用单个日历，但根据输入事件的颜色是否需要很快，延迟付款或已付款来更改输入事件的颜色?

1. Use a single calendar but change the color of the entry event based on if its Required soon, Late Payment or Paid?

更重要的是，付款后，该项目会在工作表中标记出来，但仍必须从我的日历中删除或用绿色进行更新，以表明它现在已经付款.

More importantly, Once an item has been paid this is flagged in the sheet but it still has to be removed from my calendar or updated with a green colour to indicate its now been paid.

推荐答案

您可以使用这里.

只需在每个 if 语句中实现所需的事件颜色，例如

Just implement in each of your if statements an event color as desired, e.g.

    ...
    else if (statt == "Paid" ) {
       var Debtor = shift[0];
       var startTime = shift[6];
       var endTime = shift[6];
       var event = eventCalPaid.createAllDayEvent(Debtor, startTime, endTime+1);
       event.setColor(CalendarApp.EventColor.GREEN);      
    }
    ...

更新

如果您想先验证一个事件是否存在，并修改是否存在-您可以按照以下步骤进行操作:

if you want to verify first either an event exists and modify if does - you can do it as following:

    ...
    else if (statt == "Paid" ) {
       var Debtor = shift[0];
       var startTime = shift[6];
       var endTime = shift[6];
       var events = eventCal.getEvents(startTime, endTime, {search : Debtor});
       var event = events[0];
       if(!event){
        var event = eventCal.createAllDayEvent(Debtor, startTime, endTime);
       }
       event.setColor(CalendarApp.EventColor.GREEN);      
    }
    ...

请注意 endTime + 1 ，如果时间不对应.

这篇关于Google表格到日历-更新上一个事件脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！