在Google电子表格中使用Apps脚本进行存档 [英] Archiving in Google Spreadsheets with Date Using Apps Script
问题描述
我有一个电子表格，其中包含具有日期和优先级的操作项。我想要创建一个函数来存档一行，如果该项目是优先级2和一周或更久。
这是我的脚本，但它的行为 - 有时它会移动错误的优先级，有时会移动错误的日期，并且它永远不会移动它应该的所有日期。最糟糕的是，它在我的Action Items工作表底部添加了大量空行，并在Archive工作表中创建了空行。
显然有些东西是错误的，但我真的没有看到它，即使经过一整天的密集调试。任何帮助将是启发和赞赏！
$ p $ function Archiver（）{
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var sheet = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（'Action Items'）; //获得工作表
var targetsheet = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（'Archive'）; //获取目标图纸
var columnF = sheet.getRange（2，6，sheet.getLastRow（） - 1，1）; //获取所有包含日期的行
var fValues = columnF.getValues（）; //获取日期的值
var columnE = sheet.getRange（2，5，sheet.getLastRow（） - 1，1）; //获取所有具有优先级的行
var eValues = columnE.getValues（）; //获取优先级的值
var day = 24 * 3600 * 1000 //计算一天中的ms
var today = parseInt（（new Date（）。setHours（0,0,0,0 ））/天）; //取得日期今天
for（var i = 0; i< fValues.length; i ++）{//重复循环
var priority = eValues [i] [0]; //在循环中设置优先级
var dataday = parseInt（fValues [i] [0] .getTime（）/ day）; //将日期列转换为毫秒
Logger.log（dataday +< =+ today-7 + - + priority）//我的日志不会记录日期
if（dataday< ; =今天-7&&优先级==P2）{//如果一个项目超过7天和优先级2 ...
targetsheet.insertRows（2,1）
//将整个源行移动到目标工作表的第二行
var rangeToMove = sheet.getRange（/ * startRow * / i + 2，/ * startColumn * / 1，/ * numRows * / 1，/ * numColumns * / sheet.getMaxColumns（））;
rangeToMove.moveTo（targetsheet.getRange（A2））;
//添加批准到列E中的目标行的日期和时间
targetsheet.getRange（M2）。setValue（Date（））;
//从源表中删除行
sheet.deleteRows（i + 2,1）;
}
}
ss.toast（继续前进，归档完成）;
}
代码，但我最初的想法是，迭代时删除行扰乱数据的顺序并造成混淆，所以我调整它复制行（而不是移动），并将要删除的行号存储在数组中，以便我们可以删除他们在循环之外。
$ b
函数Archiver（）{
var ss = SpreadsheetApp.getActiveSpreadsheet （）;
var sheet = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（'Action Items'）; //获得工作表
var targetsheet = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（'Archive'）; //获取目标图纸
var columnF = sheet.getRange（2，6，sheet.getLastRow（） - 1，1）; //获取所有包含日期的行
var fValues = columnF.getValues（）; //获取日期的值
var columnE = sheet.getRange（2，5，sheet.getLastRow（） - 1，1）; //获取所有具有优先级的行
var eValues = columnE.getValues（）; //获取优先级的值
var day = 24 * 3600 * 1000 //计算一天中的ms
var today = parseInt（（new Date（）。setHours（0,0,0,0 ））/天）; //今天获取日期
var rowsToDelete = [];
for（var i = 0; i< fValues.length; i ++）{//重复循环
var priority = eValues [i] [0]; //在循环中设置优先级
var dataday = parseInt（fValues [i] [0] .getTime（）/ day）; //将日期列转换为毫秒
Logger.log（dataday +< =+（today-7）+ - + priority）//我的日志不会记录日期
if（ dataday< = today-7&& priority>P2）{//如果一个项目超过7天，优先级2 ...
targetsheet.insertRows（2,1）
//将整个源行移动到目标工作表的第二行
var rangeToMove = sheet.getRange（/ * startRow * / i + 2，/ * startColumn * / 1，/ * numRows * / 1， / * numColumns * / sheet.getMaxColumns（））;
rangeToMove.copyTo（targetsheet.getRange（A2））;
//添加批准到列E中的目标行的日期和时间
targetsheet.getRange（M2）。setValue（Date（））;
//从源表中删除行
rowsToDelete.push（i + 2）;
}
}
rowsToDelete.reverse（）; $ var b $ for（var j = 0; j
}
I have a spreadsheet with action items that have dates and priorities attached to them. I want to create a function that archives a row if the item is a Priority 2 and a week old or more.
This is my script, but it's acting out -- sometimes it moves the wrong priorities, sometimes it moves the wrong dates, and it never moves all the dates that it should. Worst, it adds tons of empty rows at the bottom of my "Action Items" sheet, and creates empty rows in the "Archive" sheet as well.
Obviously something is wrong, but I really don't see it, even after a day of intense debugging. Any help would be enlightening and appreciated!
function Archiver() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Action Items'); // get the sheet
var targetsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Archive'); // get the target sheet
var columnF = sheet.getRange(2, 6, sheet.getLastRow()-1, 1); // get all the rows with dates
var fValues = columnF.getValues(); // get the values of dates
var columnE = sheet.getRange(2, 5, sheet.getLastRow()-1, 1); // get all the rows with priorities
var eValues = columnE.getValues(); // get the values of priorities
var day = 24*3600*1000 // calculate ms in a day
var today = parseInt((new Date().setHours(0,0,0,0))/day); // get date today
for (var i = 0; i < fValues.length; i++) { // repeat loop
var priority = eValues[i][0]; // set priority in loop
var dataday = parseInt(fValues[i][0].getTime()/day); // convert date column into miliseconds
Logger.log(dataday+" <= " + today-7 + " - " + priority) // my log isn't picking up day
if (dataday <= today-7 && priority == "P2") { // if an item is more than 7 days old and Priority 2...
targetsheet.insertRows(2,1)
// move the entire source row to the second row of target sheet
var rangeToMove = sheet.getRange(/*startRow*/ i + 2, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ sheet.getMaxColumns());
rangeToMove.moveTo(targetsheet.getRange("A2"));
// add date and time of when approved to target row in column E
targetsheet.getRange("M2").setValue(Date());
// delete row from source sheet
sheet.deleteRows(i + 2,1);
}
}
ss.toast("Move along.", "Archiving Complete.");
}
I didn't debug your code, but my initial thought is that deleting rows while iterating disturbs the order of the data and creates confusion, so I adjusted it to copy the rows (instead of moving) and store the row numbers to be deleted in an array so we can delete them outside the loop. I did a quick test and this seems to work.
function Archiver() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Action Items'); // get the sheet
var targetsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Archive'); // get the target sheet
var columnF = sheet.getRange(2, 6, sheet.getLastRow()-1, 1); // get all the rows with dates
var fValues = columnF.getValues(); // get the values of dates
var columnE = sheet.getRange(2, 5, sheet.getLastRow()-1, 1); // get all the rows with priorities
var eValues = columnE.getValues(); // get the values of priorities
var day = 24*3600*1000 // calculate ms in a day
var today = parseInt((new Date().setHours(0,0,0,0))/day); // get date today
var rowsToDelete = [];
for (var i = 0; i < fValues.length; i++) { // repeat loop
var priority = eValues[i][0]; // set priority in loop
var dataday = parseInt(fValues[i][0].getTime()/day); // convert date column into miliseconds
Logger.log(dataday+" <= " + (today-7) + " - " + priority) // my log isn't picking up day
if (dataday <= today-7 && priority == "P2") { // if an item is more than 7 days old and Priority 2...
targetsheet.insertRows(2,1)
// move the entire source row to the second row of target sheet
var rangeToMove = sheet.getRange(/*startRow*/ i + 2, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ sheet.getMaxColumns());
rangeToMove.copyTo(targetsheet.getRange("A2"));
// add date and time of when approved to target row in column E
targetsheet.getRange("M2").setValue(Date());
// delete row from source sheet
rowsToDelete.push(i+2);
}
}
rowsToDelete.reverse();
for (var j = 0; j < rowsToDelete.length; j++) { sheet.deleteRow(rowsToDelete[j]); }
ss.toast("Move along.", "Archiving Complete.");
}
这篇关于在Google电子表格中使用Apps脚本进行存档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
