Spreadsheet.flush无法在我的Google Apps脚本上正常工作 [英] Spreadsheet.flush is not working properly on my Google Apps Script

问题描述

这是我的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:

1. 根据Gsheet2上的员工人数(我目前有800多名员工)复制Gsheet1，并将其转储到google驱动器文件夹中.
2. 复制后，会将已完成"复制状态设置为Gsheet2.
3. 更改复制工作表的修订版属性，然后将其发布到Web.
4. 获取已发布网站(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;

之所以这样，是因为您已经开始从 第二行. 如果您要从第三个开始，则应该var getLastRow = mastersheet.getLastRow()-2; ...

The reason why is because you've started to get your data from the second row. If you would start at the third, you should do var getLastRow = mastersheet.getLastRow()-2; ...

这篇关于Spreadsheet.flush无法在我的Google Apps脚本上正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！