使用缓存提高脚本的速度 [英] Increasing speed of script using cache
问题描述
我有一个脚本可以获取电子表格的所有值，并使用这些值在日历中创建条目。
然而，运行时间太长，超时。
它不习惯花太多时间，因为没有足够的条目，但现在有足够的条目在它超时之前无法完成，所以我需要提高速度。
我相信它运行得很慢的原因是因为循环遍历电子表格的每一行，并且在每个循环的末尾它写入日历事件。我认为这会增加执行时间，因为它必须一遍又一遍地重新连接到日历。我认为这大大增加了执行时间。
我相信我可以通过缓存来减少这种情况，但我甚至没有丝毫的线索知道它是如何工作的。
这是我的代码：
/ **
*将电子表格中的事件导出到日历
* /
函数exportEvents（）{
var sheet = SpreadsheetApp.getActiveSheet（）;
var headerRows = 1; //标题信息的行数（跳过）
var range = sheet.getDataRange（）;
var data = range.getValues（）;
var calId =trhcom7eiadkcn39mg9d0hfceg@group.calendar.google.com;
var cal = CalendarApp.getCalendarById（calId）;
for（i in data）{
if（i
var date = new Date（row [9]）;
if（！（isValidDate（date）））continue; //跳过没有日期的行
var title = row [19] + - + row [3] + - + row [1] + - + row [2];
var id = row [31];
//检查事件是否已经存在，如果它存在，删除它
try {
var event = cal.getEventSeriesById（id）;
event.deleteEventSeries（）;
row [31] =''; //删除事件ID
}
catch（e）{
//什么也不做 - 我们只是想避免在事件不存在的情况下发生异常
}
var newEvent = cal.createAllDayEvent（title，date）.addEmailReminder（4320）.addEmailReminder（60）.addSmsReminder（4320）.addSmsReminder（60）.getId（）;
row [31] = newEvent; //用事件ID
}更新数据数组
i = 0;
for（i in data）{
if（i
var date = new Date（row [13]）;
if（！（isValidDate（date）））continue; //跳过没有日期的行
var title =期望付款日期：+ row [19];
var id = row [32];
//检查事件是否已经存在，如果它存在，删除它
try {
var event = cal.getEventSeriesById（id）;
event.deleteEventSeries（）;
row [32] =''; //删除事件ID
}
catch（e）{
//什么也不做 - 我们只是想避免在事件不存在的情况下发生异常
}
date.setDate（date.getDate（）+ 12）;
var newEvent = cal.createAllDayEvent（title，date）.addEmailReminder（4320）.addEmailReminder（60）.addSmsReminder（4320）.addSmsReminder（60）.getId（）;
row [32] = newEvent; //用事件ID
}
更新数据数组//将所有事件ID记录到电子表格
range.setValues（data）;
};
我试图使用此页面，但我甚至不知道从哪里开始。
缓存存储在本地还是存储在服务器上？我如何访问它？什么是钥匙，我在哪里找到它？我使用哪个网址？这将如何增加我的速度？
我觉得这很简单，但我不明白这个概念。
更新：在做了一些研究后，我不确定缓存可以帮助我，因为它不会获取需要很长时间而是需要创建的数据。
也许我应该想办法在循环结束时立即将所有事件写入日历，但我不知道如何要做到这一点。
你的问题包含很多问题，但你已经自己回答了一些问题......; - ）它确实不是阅读工作表数据的问题（你已经用getDataRange（）。getValues（）方法正确地做了，但是需要很长时间才能创建事件...
不幸的是，没有办法加快速度，我们唯一能做的就是减少大小批量，让脚本每10分钟自动运行一次，直到创建所有事件。 / p>
没有什么真正复杂的，下面是一个显示过程的脚本：
function createEvents（）{
//检查脚本是否第一次运行，
//如果是，则创建触发器和属性Service.getScriptProperties（）脚本将使用
//一个开始索引知道它是否必须继续
if（PropertiesService.getScriptProperties（）。getKeys（）。length == 0 ）{
PropertiesService.getScriptProperties（）。setProperties（{'startRow'：0}）;
ScriptApp.newTrigger（'createEvents'）。timeBased（）。everyMinutes（10）.create（）;
}
//当我们启动
时初始化所有变量var startRow = Number（PropertiesService.getScriptProperties（）。getProperty（'startRow'））;
var sheet = SpreadsheetApp.getActiveSheet（）;
var headerRows = 1; //标题信息的行数（跳过）
var range = sheet.getDataRange（）;
var data = range.getValues（）;
var calId =trhcom7eiadkcn39mg9d0hfceg@group.calendar.google.com;
var cal = CalendarApp.getCalendarById（calId）;
var counter = 0
for（var i = tstartRow; i< data.length; i ++）{
counter ++;
if（counter == 30）{break}
if（i< headerRows）continue; //跳过标题行（s）
var row = data [i];
...
...继续你自己的代码
}
//更新电子表格
// if i == data.length然后终止触发器并最后给自己发一条消息
//告诉你脚本已经成功完成。
//杀死当前触发器如下所示：
var trigger = ScriptApp.getProjectTriggers（）[0];
ScriptApp.deleteTrigger（trigger）;
祝您好运。
I have a script that gets all the values of a spreadsheet and uses those values to create entries in a calendar.
However, it takes too long to run and times out.
It didn't used to take that long because there wasn't enough entries, but now there's enough entries that it cant finish before it times out, so I need to increase the speed.
I believe the reason it runs so slow is because there's a loop that runs through every row of the spreadsheet and at the end of every loop it writes a calendar event. I think it's this that adds to the execution time because it has to reconnect to the calendar over and over again. I think this massively adds to the execution time.
I believe I can reduce this with caching but I have not even the slightest clue how that works.
Here is my code:
/**
* Export events from spreadsheet to calendar
*/
function exportEvents() {
var sheet = SpreadsheetApp.getActiveSheet();
var headerRows = 1; // Number of rows of header info (to skip)
var range = sheet.getDataRange();
var data = range.getValues();
var calId = "trhcom7eiadkcn39mg9d0hfceg@group.calendar.google.com";
var cal = CalendarApp.getCalendarById(calId);
for (i in data) {
if (i < headerRows) continue; // Skip header row(s)
var row = data[i];
var date = new Date(row[9]);
if (!(isValidDate(date))) continue; // Skip rows without a date
var title = row[19]+" - "+row[3]+" - "+row[1]+" - "+row[2];
var id = row[31];
// Check if event already exists, delete it if it does
try {
var event = cal.getEventSeriesById(id);
event.deleteEventSeries();
row[31] = ''; // Remove event ID
}
catch (e) {
// do nothing - we just want to avoid the exception when event doesn't exist
}
var newEvent = cal.createAllDayEvent(title, date).addEmailReminder(4320).addEmailReminder(60).addSmsReminder(4320).addSmsReminder(60).getId();
row[31] = newEvent; // Update the data array with event ID
}
i=0;
for (i in data) {
if (i < headerRows) continue; // Skip header row(s)
var row = data[i];
var date = new Date(row[13]);
if (!(isValidDate(date))) continue; // Skip rows without a date
var title = "Expected Pay Date: "+row[19];
var id = row[32];
// Check if event already exists, delete it if it does
try {
var event = cal.getEventSeriesById(id);
event.deleteEventSeries();
row[32] = ''; // Remove event ID
}
catch (e) {
// do nothing - we just want to avoid the exception when event doesn't exist
}
date.setDate(date.getDate() + 12);
var newEvent = cal.createAllDayEvent(title, date).addEmailReminder(4320).addEmailReminder(60).addSmsReminder(4320).addSmsReminder(60).getId();
row[32] = newEvent; // Update the data array with event ID
}
// Record all event IDs to spreadsheet
range.setValues(data);
};
I'm trying to use the information from this page but I don't even know where to begin.
Is the cache stored locally or on the server? How do I access it? What is a key and where do I find it? What url do I use? How will this end up increasing my speed?
I feel like this is simple but I just don't grasp the concept.
Update: After doing some research I'm not sure a cache can help me since it's not getting data that's taking a long time but rather creating it.
Maybe instead I should be trying to figure out a way to simply write all the events to the calendar at once at the end of the loop but I wouldn't know how to do that either.
Your question contains a lot of question but you already answered a couple of them yourself... ;-) it is indeed not a matter of reading sheet data (which you already do the right way using getDataRange().getValues() ) but a problem with the event creation that takes a long time...
Unfortunately there is no way to speed that up, the only thing we can do is proceed by reduced size batch and let the script run automatically every 10 minutes or so until all the events are created.
Nothing really complicated, here is a script that shows the process :
function createEvents() {
// check if the script runs for the first time or not,
// if so, create the trigger and PropertiesService.getScriptProperties() the script will use
// a start index to know were from it has to continue
if(PropertiesService.getScriptProperties().getKeys().length==0){
PropertiesService.getScriptProperties().setProperties({'startRow':0 });
ScriptApp.newTrigger('createEvents').timeBased().everyMinutes(10).create();
}
// initialize all variables when we start
var startRow = Number(PropertiesService.getScriptProperties().getProperty('startRow'));
var sheet = SpreadsheetApp.getActiveSheet();
var headerRows = 1; // Number of rows of header info (to skip)
var range = sheet.getDataRange();
var data = range.getValues();
var calId = "trhcom7eiadkcn39mg9d0hfceg@group.calendar.google.com";
var cal = CalendarApp.getCalendarById(calId);
var counter = 0
for (var i=tstartRow ; i < data.length ; i++) {
counter++ ;
if(counter == 30){ break }
if (i < headerRows) continue; // Skip header row(s)
var row = data[i];
...
... continue your own code
}
// update the spreadsheet
// if i== data.length then kill the trigger and eventually send yourself a message
// to tell you that the script has finished successfully .
// killing the current trigger goes like this :
var trigger = ScriptApp.getProjectTriggers()[0];
ScriptApp.deleteTrigger(trigger);
Good luck.
这篇关于使用缓存提高脚本的速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
