问题描述
这是我的2个电子表格:
Here are my 2 spreadsheets:
Gsheet1:用于复制的Googlesheet(仅员工编号和员工姓名会更改)
Gsheet1: Googlesheet for replication (only employee # and employee name would change)
Gsheet2:配置表(包含员工编号,员工姓名,复制状态,pubHTML链接),该表将在Gsheet1上提供员工编号和员工姓名
Gsheet2: Config sheet (contains the employee #, employee name, replication status, pubHTML link) that would feed employee # and employee name on Gsheet1
因此,我在下面的代码中具有以下功能:
So I have this code that have these functionalities below:
- 根据Gsheet2上的员工人数(我目前有800多名员工)复制Gsheet1,并将其转储到google驱动器文件夹中.
- 复制后,会将已完成"复制状态设置为Gsheet2.
- 更改复制工作表的修订版属性,然后将其发布到Web.
- 获取已发布网站(pubhtml)的链接,并将其放在Gsheet2 pubHTML链接列中.
发生的事情是,当我尝试在下面的logger.log中记录此代码的结果时,假设我的Gsheet2上有2条记录,它循环了3次,循环中的第一个和第三个记录是相同的.
What happens is when I try to logger.log the results of this code below assuming that I have 2 records on my Gsheet2, it loops three times, the first and third record in loop are the same.
var TemplatesFromDrive = DriveApp.getFolderById(SpreadsheetApp.getActive().getSheetByName("Master Config").getRange("B2").getValue()).getFiles();
while (TemplatesFromDrive.hasNext()) {
var File = TemplatesFromDrive.next();
Logger.log(File.getName());
我在想是否是因为我缺少Spreadsheet.flush()而导致的.我可以将其放在代码上的最佳位置是什么,以便我的循环可以正常工作?下面是我的完整代码.
I was thinking if it's because of the Spreadsheet.flush() that I'm missing. Where is the best place where I can put it on my code so my loop will work properly? Below is my full code.
function replicateCards() {
var ss = SpreadsheetApp.openById('Gsheet2-xxxx');
var copyCard = SpreadsheetApp.openById('Gsheet1-xxxx');
var getID = DriveApp.getFileById(copyCard.getId())
var card = copyCard.getSheetByName("Card");
var mastersheet = ss.getSheetByName("Mastersheet");
var employeeNumber2 = ss.getRange("A2:A").getValues;
var getLastRow = mastersheet.getLastRow();
var destinationFolder = DriveApp.getFolderById('googledrivefolder-xxx');
var changeColorToGrayList = card.getRangeList(['C7', 'E7', 'G7', 'I7', 'K7', 'M7', 'O7', 'Q7',
'C9', 'E9', 'G9', 'I9', 'K9', 'M9', 'O9', 'Q9',
'C11', 'E11', 'G11', 'I11', 'K11', 'M11', 'O11', 'Q11']);
var setValueToZero = card.getRangeList(['C8', 'E8', 'G8', 'I8', 'K8', 'M8', 'O8', 'Q8',
'C10', 'E10', 'G10', 'I10', 'K10', 'M10', 'O10', 'Q10',
'C12', 'E12', 'G12', 'I12', 'K12', 'M12', 'O12', 'Q12']);
for (i = 1; i < getLastRow; i++) {
var badgeStatus = mastersheet.getRange(i + 1, 5).getValue();
if (badgeStatus == "") {
var employeeNumber = mastersheet.getRange(i + 1, 1).getValue();
var employeeName = mastersheet.getRange(i + 1, 2).getValue();
copyCard.getRange("H3").setValue(employeeNumber);
copyCard.getRange("C3").setValue(employeeName);
SpreadsheetApp.flush();
getID.makeCopy(employeeNumber, destinationFolder);
mastersheet.getRange(1 + i, 5).setValue("completed");
SpreadsheetApp.flush();
var files = DriveApp.getFolderById(SpreadsheetApp.openById("Gsheet1-xxxx").getSheetByName("Config Sheet").getRange("B1").getValue()).getFiles();
while (files.hasNext()) {
var file = files.next();
Logger.log(file.getName());
var Found = false;
for (var j = 0; j < employeeNumber2.length; i++) {
if (employeeNumber2[j][0] == file.getName()) {
Found = true;
}
}
if (Found) {
continue;
}
try {
var fileId = file.getId();
var fileName = file.getName();
var revisions = Drive.Revisions.list(fileId);
var lastRevisionId = revisions.items[revisions.items.length - 1].id;
// get the resource and set the publish parameters
var resource = Drive.Revisions.get(fileId, lastRevisionId);
// Logger.log(resource);
resource.published = true;
resource.publishAuto = true;
resource.publishedOutsideDomain = true;
// publish to the web
Drive.Revisions.update(resource, fileId, lastRevisionId);
SpreadsheetApp.flush();
var openByID = SpreadsheetApp.openById(fileId);
SpreadsheetApp.flush();
var googleDriveSheet = openByID.getUrl().replace("edit", "pubhtml"); // or replace("edit", "pub");
SpreadsheetApp.flush();
mastersheet.getRange(1 + j, 9).setValue(googleDriveSheet);
SpreadsheetApp.flush();
} catch (err) {
Logger.log(err);
}
}
}
}
}
推荐答案
将var getLastRow = mastersheet.getLastRow();
更改为var getLastRow = mastersheet.getLastRow()-1;
这篇关于Spreadsheet.flush无法在我的Google Apps脚本上正常工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!