javascript - Google Apps 脚本 - 两个函数/脚本在单独运行但不组合时有效

Question

我正在编写一个更新 XLS 文件值的脚本。我只会添加我认为与问题相关的内容，但如果您需要更多信息，请告诉我。

我首先提交了一个表格，上面写着要添加的值、添加的位置以及添加的数量。然后脚本在提交表单时运行。

它首先将 .XLS 文件转换为 .CSV 文件，然后将 CSV 文件中的数据添加到谷歌电子表格中，同时添加我告诉它的值，以便一切正常。所有这一切都很好，我可以看到每次都在 Google 表格中更新了值，当它发生时以及在代码运行之后。

完成后，我想将 Google Sheet 转换回 XLS 文件，并将其放回原来所在的文件夹。我第一次这样做时，XLS 文件中的值已更新，但第二次和之后的每次，它“落后一个会话”。第二次我没有在新的 XLS 文件中获得更新的值，第三次我从第二次获得更新的值，以此类推。这只发生在 XLS 文件上，而不是 Google Sheet。

这仅在我手动运行导出 XLS 文件的代码/函数时才会发生，一切正常。但是，如果我在主代码之后添加该函数，问题就会发生。

我试图在函数之间暂停代码 10 秒，但我得到了相同的结果。

---

由于正在添加的所有值在谷歌表中都可以正常工作，因此我只需添加文件类型转换的代码。我将用 // 评论两者之间发生的事情

这只是同时运行的函数，但没有给我一个带有最新更新值的 XLS 文件：

function test() {    

  newValuesToDatabase();
  updateALLITEMS();

}

添加新值的函数（到不相关的其他电子表格和将成为 XLS 文件的电子表格）

function newValuesToDatabase() {


// FIRST FINDS OUT WHAT TO UPDATE FROM THE FORM SHEET 
// Ends up with "formX" array seen later in the code


// Opens and gets the data from another sheet I also want to update (Irrelevant sheet) 
// This ends up with the "itemDataBase" Array used later


// Gets the XLS file and turn it into CSV


    var workFolder = DriveApp.getFolderById( "(workFolderid)" ),
        backupFolder = DriveApp.getFolderById( "(backupFolderid)" );

    var oauthToken = ScriptApp.getOAuthToken(),
        sourceFolder = DriveApp.getFolderById( "(sourceFolderid)" ),
        mimes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];



    for (var m = 0; m < mimes.length; m++) {

      var files = sourceFolder.getFilesByType(mimes[m]);

        while (files.hasNext()) {

          var sourceFile = files.next();

          if (sourceFile.getName() == "ALL ITEMS") {

            console.log("File found")


            //Creates a backupfile to be put in a different folder
            var backupDate = (new Date).getDate() + ", " 
                           + (new Date).toString().split(" ")[1] + " " 
                           + (new Date).getYear();

            sourceFile.makeCopy(backupFolder).setName("backup on " + backupDate);
            sourceFile.setTrashed(true);


            var googleSheet = JSON.parse(UrlFetchApp.fetch(
                "https://www.googleapis.com/upload/drive/v2/files?uploadType=media&convert=true", {
                    method: "POST",
                    contentType: "application/vnd.ms-excel",
                    payload: sourceFile.getBlob().getBytes(),
                    headers: {
                        "Authorization": "Bearer " + oauthToken
                    }
                }
            ).getContentText());   


            // The exportLinks object has a link to the converted CSV file
            var targetFile = UrlFetchApp.fetch(
                googleSheet.exportLinks["text/csv"], {
                    method: "GET",
                    headers: {
                        "Authorization": "Bearer " + oauthToken
                    }
                });

            // Save the CSV file in a folder to be deleted in the end of the script when done with it
            var csvFile = workFolder.createFile(targetFile.getBlob()).setName(sourceFile.getName() + ".csv");

          }
        }
      } 





    // Finds the google sheet that later will be converted back to XLS:


     var checkSSFile = workFolder.getFilesByName("Update Items (ikke rør)");
     var checkID = checkSSFile.next().getId().toString();

     var addSpreadSheet = SpreadsheetApp.openById(checkID),
         addSheet = addSpreadSheet.getSheetByName("ALL ITEMS");



     // Gets the CSV file and makes an array from it

     var csvFile = workFolder.getFilesByType(MimeType.CSV).next();

     if (csvFile.getName() == "ALL ITEMS.csv") {

       var csvData = Utilities.parseCsv(csvFile.getBlob().getDataAsString());

     }



     function updateAmount() {  // ( This function is ran at the bottom of code )



       //Code first adds stuff to the irrelevant spreadsheet mentioned in the beginning of this code



       // Then gets the sheet that will be converted to XLS:

         var range = addSheet.getRange(1, 1, addSheet.getLastRow()+1, 2);
         range.clearContent(); //Empties sheet first so there are no overlap when adding everything over again


         //Code then adds the old and new values into that sheet in the right order and way

     }




   // Here is where the code is ran dependent on what the form says.


     if (formX[0][1] == "Add a new item of something we have from before") {

       updateAmount();

     } else if (formX[0][1] == "Add a completely new item") {

       //I have not added this function/code yet

     } else {

       console.log("Could not find a mode in the form submission");

     }


     csvFile.setTrashed(true); //Then trashes the csv file in the end
}

然后将其转换回来并将其放置在正确位置的函数：

function updateALLITEMS() {

  var workFolder = DriveApp.getFolderById( "(workFolderid)" );
  var checkSS = workFolder.getFilesByName("Update Items (ikke rør)");
  var checkID = checkSS.next().getId().toString();

  var sourceFolder  = DriveApp.getFolderById( "(sourceFolderid)" ),
      file          = DriveApp.getFileById(checkID),
      url           = 'https://docs.google.com/spreadsheets/d/'+checkID+'/export?format=xlsx';
  var token         = ScriptApp.getOAuthToken();

  var newAllItems      = UrlFetchApp.fetch(url, {
    method: "GET",
    payload: file.getBlob().getBytes(),
    headers: {
        'Authorization': 'Bearer ' +  token
      }
  });


  // Then add "ALL ITEMS" XLS file back to where it was
  sourceFolder.createFile(newAllItems.getBlob()).setName("ALL ITEMS"); 


   //This deletes files that appear on the main GDrive folder, don't know why. 
   //These files are named "Unknown", and are copies of the Google Sheet I'm working on

  var ss = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS); 
  while (ss.hasNext()) {
    var sht = ss.next();
    if (sht.getName() == "Untitled") {

      var shtID = sht.getId();
      var checkSht1 = SpreadsheetApp.openById(shtID).getSheets();
      if ( checkSht1.length == 1 ) { 

        if (checkSht1[0].getRange(1, 1).getValue() == "BARCODE") {

          sht.setTrashed(true);


        }
      }
    }
  }
}

非常感谢您阅读所有内容，我已经被这个问题困扰太久了。我尝试了很多不同的事情，但总是得到相同的结果（除非我手动开始一个，然后当第一个完成时另一个）。希望代码不会太混乱，我是编码新手:)

标签： javascriptgoogle-apps-scriptgoogle-sheets

Answer 1

您是否在第一个函数的末尾尝试过 SpreadsheetApp.flush ？