Google Apps脚本可作为Web应用程序使用，但不能作为电子表格脚本使用

micah 发表于 Dev

弥迦

我已经将一个独立脚本部署为Web App。我的代码与Web应用程序完全一样。但是，我的目标是在电子表格项目中使用该代码。

我将代码复制并粘贴到Spreadsheet脚本中，并进行了一些更改，以使其在弹出的“模态”对话框窗口中工作，但是它不能像已部署的Web应用程序那样工作。该代码似乎没有将任何数据返回给Code.gs。在沿代码路径的各个点运行警报之后，我发现了故障点，但不确定如何解决。

来自运行中的WebApp的代码（2个文件：Code.gs和form.html）：

## Code.gs file ##
function doGet() {
  return HtmlService.createHtmlOutputFromFile('form')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

function uploadFileToDrive(base64Data, fileName, totalFiles, finalCount) {
  try{
    var splitBase = base64Data.split(','),
        type = splitBase[0].split(';')[0].replace('data:','');

    var byteCharacters = Utilities.base64Decode(splitBase[1]);
    var ss = Utilities.newBlob(byteCharacters, type);
    ss.setName(fileName);

    var dropbox = "stuff"; // Folder Name
    var folder, folders = DriveApp.getFoldersByName(dropbox); // Get folders by name

    if (folders.hasNext()) {
      folder = folders.next();
    } else {
      folder = DriveApp.createFolder(dropbox);
    }
    var file  = folder.createFile(ss);

    // BEGIN LOGGING FILE NAMES AND IDs
    var dropboxId = "SOME_FOLDER_ID_HERE" // Folder ID
    var theFolder = DriveApp.getFolderById(dropboxId); // Get folder by ID
    var list     = [];
    var theBlobs = [];
    if (totalFiles == finalCount) {
      var files = theFolder.getFiles();
      while (files.hasNext()) {
        var theFile = files.next();
        list.push(theFile.getId());
      }
      for (var i = 0; i < list.length; i++) {
        Logger.log(DriveApp.getFileById(list[i]).getName() + " : " + list[i]);
        theBlobs.push(DriveApp.getFileById(list[i]).getBlob());
      }
      Logger.log(theBlobs);
      // END LOGGING FILE NAMES AND IDs


      // BEGIN ZIPPING UP FILES
        var newZip = theFolder.createFile(Utilities.zip(theBlobs, 'zippedFiles.zip'));
        var zipId  = newZip.getId();
        Logger.log("Zip Id: " + zipId);
      // END ZIPPING UP FILES


      // BEGIN TRASHING UPLOADED FILES
      for (var i = 0; i < list.length; i++) {
        DriveApp.getFileById(list[i]).setTrashed(true);
      }
      // END TRASHING UPLOADED FILES
    }

    return file.getName();
  }catch(e){
    return 'Error: ' + e.toString();
  }
}


## form.html ##
<body>
  <div id="formcontainer">

    <form id="myForm">

      <label for="myFile">Upload File(s):</label><br />

      <input type="file" name="filename" id="myFile" multiple />

      <input type="button" class="blue" value="Submit" onclick="iteratorFileUpload()" /><br /><br />

    </form>
  </div>

  <div id="output"></div>

  <div id="progressbar">
    <div class="progress-label"></div>
  </div>

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/themes/smoothness/jquery-ui.css">
<link rel="stylesheet" href="https://googledrive.com/host/0By0COpjNTZPnZTBvVGZOSFRhREE/add-ons.css">
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js"></script>

<script>
var numUploads   = {};
numUploads.done  = 0 ;
numUploads.total = 0 ;

// Upload the files into a folder in drive...set to send them all to one folder (specificed in the .gs file)
function iteratorFileUpload() {
    var allFiles = document.getElementById('myFile').files;

    if (allFiles.length == 0) {
        alert('No file selected!');
    } else { // Show Progress Bar
        var myCount = 0; // Begin count to compare loops through
        numUploads.total = allFiles.length;
        $('#progressbar').progressbar({
        value : false
        });
        $(".progress-label").html('Preparing files..');
        // Send a file at a time
        for (var i = 0; i < allFiles.length; i++) {
            myCount++; // Increment count each time before sending the file to drive
            sendFileToDrive(allFiles[i], allFiles.length, myCount);
        }
    }
}

function sendFileToDrive(file, totalFiles, newCount) {
    var reader = new FileReader();
    reader.onload = function (e) {
        var content = reader.result;
        google.script.run.withSuccessHandler(updateProgressbar).uploadFileToDrive(content, file.name, totalFiles, newCount);
    }
    reader.readAsDataURL(file);
}

function updateProgressbar( idUpdate ){
   numUploads.done++;
   var porc = Math.ceil((numUploads.done / numUploads.total)*100);
   $("#progressbar").progressbar({value: porc });
   $(".progress-label").text(numUploads.done +'/'+ numUploads.total);
   if( numUploads.done == numUploads.total ){
      numUploads.done = 0;
   };
}

function fileUploaded(status) {
  document.getElementById('myForm').style.display = 'none';
  document.getElementById('output').innerHTML = status;
}
</script>

  <style>
    body {
      max-width: 400px;
      padding: 20px;
      margin: auto;
    }
    input {
      display: inline-block;
      width: 100%;
      padding: 5px 0px 5px 5px;
      margin-bottom: 10px;
      -webkit-box-sizing: border-box;
      ‌ -moz-box-sizing: border-box;
      box-sizing: border-box;
    }
    select {
      margin: 5px 0px 15px 0px;
    }
    input[type="submit"] {
      width: auto !important;
      display: block !important;
    }
    input[type="file"] {
      padding: 5px 0px 15px 0px !important;
    }
#progressbar{
    width: 100%;
    text-align: center;
    overflow: hidden;
    position: relative;
    vertical-align: middle;

}
.progress-label {
      float: left;
margin-top: 5px;
      font-weight: bold;
      text-shadow: 1px 1px 0 #fff;
          width: 100%;
    height: 100%;
    position: absolute;
    vertical-align: middle;
    }
  </style>
</body>

当部署为Spreadsheet容器绑定的应用程序时，代码如下（2个文件：Code.gs和form.html）：

## Code.gs ##
function uploadFiles() {

  function doGet() {
    return SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile('form').setSandboxMode(HtmlService.SandboxMode.IFRAME), "Upload Files");
  }

  doGet();

  function uploadFileToDrive(base64Data, fileName, totalFiles, finalCount) {
    try{
      var splitBase = base64Data.split(','),
          type = splitBase[0].split(';')[0].replace('data:','');

      var byteCharacters = Utilities.base64Decode(splitBase[1]);
      var ss = Utilities.newBlob(byteCharacters, type);
      ss.setName(fileName);

      var dropbox = "stuff"; // Folder Name
      var folder, folders = DriveApp.getFoldersByName(dropbox); // Get folders by name

      if (folders.hasNext()) {
        folder = folders.next();
      } else {
        folder = DriveApp.createFolder(dropbox);
      }
      var file  = folder.createFile(ss);

      // BEGIN LOGGING FILE NAMES AND IDs
      var dropboxId = "SOME_FOLDER_ID_HERE" // Folder ID
      var theFolder = DriveApp.getFolderById(dropboxId); // Get folder by ID
      var list     = [];
      var theBlobs = [];
      if (totalFiles == finalCount) {
        var files = theFolder.getFiles();
        while (files.hasNext()) {
          var theFile = files.next();
          list.push(theFile.getId());
        }
        for (var i = 0; i < list.length; i++) {
          Logger.log(DriveApp.getFileById(list[i]).getName() + " : " + list[i]);
          theBlobs.push(DriveApp.getFileById(list[i]).getBlob());
        }
        Logger.log(theBlobs);
        // END LOGGING FILE NAMES AND IDs


        // BEGIN ZIPPING UP FILES
          var newZip = theFolder.createFile(Utilities.zip(theBlobs, 'zippedFiles.zip'));
          var zipId  = newZip.getId();
          Logger.log("Zip Id: " + zipId);
        // END ZIPPING UP FILES


        // BEGIN TRASHING UPLOADED FILES
        for (var i = 0; i < list.length; i++) {
          DriveApp.getFileById(list[i]).setTrashed(true);
        }
        // END TRASHING UPLOADED FILES
      }

      return file.getName();
    }catch(e){
      return 'Error: ' + e.toString();
    }
  }
}


## form.html ##
<body>
  <div id="formcontainer">

    <form id="myForm">

      <label for="myFile">Upload File(s):</label><br />

      <input type="file" name="filename" id="myFile" multiple />

      <input type="button" class="blue" value="Submit" onclick="iteratorFileUpload()" /><br /><br />

    </form>
  </div>

  <div id="output"></div>

  <div id="progressbar">
    <div class="progress-label"></div>
  </div>

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/themes/smoothness/jquery-ui.css">
<link rel="stylesheet" href="https://googledrive.com/host/0By0COpjNTZPnZTBvVGZOSFRhREE/add-ons.css">
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js"></script>

<script>
var numUploads   = {};
numUploads.done  = 0 ;
numUploads.total = 0 ;

// Upload the files into a folder in drive...set to send them all to one folder (specificed in the .gs file)
function iteratorFileUpload() {
    var allFiles = document.getElementById('myFile').files;

    if (allFiles.length == 0) {
        alert('No file selected!');
    } else { // Show Progress Bar
        var myCount = 0; // Begin count to compare loops through
        numUploads.total = allFiles.length;
        $('#progressbar').progressbar({
        value : false
        });
        $(".progress-label").html('Preparing files..');
        // Send a file at a time
        for (var i = 0; i < allFiles.length; i++) {
            myCount++; // Increment count each time before sending the file to drive
            sendFileToDrive(allFiles[i], allFiles.length, myCount);
        }
    }
}

function sendFileToDrive(file, totalFiles, newCount) {
    var reader = new FileReader();
    reader.onload = function (e) {
        var content = reader.result;
        google.script.run.withSuccessHandler(updateProgressbar).uploadFileToDrive(content, file.name, totalFiles, newCount);
    }
    reader.readAsDataURL(file);
}

function updateProgressbar( idUpdate ){
   numUploads.done++;
   var porc = Math.ceil((numUploads.done / numUploads.total)*100);
   $("#progressbar").progressbar({value: porc });
   $(".progress-label").text(numUploads.done +'/'+ numUploads.total);
   if( numUploads.done == numUploads.total ){
      numUploads.done = 0;
   };
}

/*
function updateProgressbar( idUpdate ){
   numUploads.done++;
   var porc = Math.ceil((numUploads.done / numUploads.total)*100);
   $("#progressbar").progressbar({value: porc });
   $(".progress-label").text(numUploads.done +'/'+ numUploads.total);
   if( numUploads.done == numUploads.total ){
      numUploads.done = 0;
   };
}
*/

function fileUploaded(status) {
  document.getElementById('myForm').style.display = 'none';
  document.getElementById('output').innerHTML = status;
}
</script>

  <style>
    body {
      max-width: 400px;
      padding: 20px;
      margin: auto;
    }
    input {
      display: inline-block;
      width: 100%;
      padding: 5px 0px 5px 5px;
      margin-bottom: 10px;
      -webkit-box-sizing: border-box;
      ‌ -moz-box-sizing: border-box;
      box-sizing: border-box;
    }
    select {
      margin: 5px 0px 15px 0px;
    }
    input[type="submit"] {
      width: auto !important;
      display: block !important;
    }
    input[type="file"] {
      padding: 5px 0px 15px 0px !important;
    }
#progressbar{
    width: 100%;
    text-align: center;
    overflow: hidden;
    position: relative;
    vertical-align: middle;

}
.progress-label {
      float: left;
margin-top: 5px;
      font-weight: bold;
      text-shadow: 1px 1px 0 #fff;
          width: 100%;
    height: 100%;
    position: absolute;
    vertical-align: middle;
    }
  </style>
</body>

无效代码在此处停止：google.script.run.withSuccessHandler(updateProgressbar).uploadFileToDrive(content, file.name, totalFiles, newCount);。

到那时为止，一切进展顺利，信息从一个函数传递到另一个函数。

在电子表格脚本中，文件永远不会上传到云端硬盘，并且在html页面上显示的文件的计数器永远不会增加...只会停滞不前。

我最近遇到的可能有助于解决此问题的文章是：如何使用google.script.run，就好像它最初是作为解决此问题的方法而提出的Google Apps Script HTML Service：传递变量并返回使用HTMLService / Google Apps脚本中的日期选择器的值。

提前谢谢你的帮助！！

弥迦

该解决方案非常简单，因为基础代码已经可以充当独立的Web应用程序。

唯一需要做的更改是对Code.gs文件。

拉出代码的开头，并将其保留在自己的顶层函数中：

function uploadFiles() {

  function doGet() {
    return SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile('form').setSandboxMode(HtmlService.SandboxMode.IFRAME), "Upload Files");
  }

  doGet();
}

然后将其余部分也放入自己的顶级函数中：

function uploadFileToDrive(base64Data, fileName, totalFiles, finalCount) {
  try{
    var splitBase = base64Data.split(','),
    type = splitBase[0].split(';')[0].replace('data:','');

    var byteCharacters = Utilities.base64Decode(splitBase[1]);
    var ss = Utilities.newBlob(byteCharacters, type);
    ss.setName(fileName);

    var dropbox = "stuff"; // Folder Name
    var folder, folders = DriveApp.getFoldersByName(dropbox); // Get folders by name

    if (folders.hasNext()) {
      folder = folders.next();
    } else {
      folder = DriveApp.createFolder(dropbox);
    }
    var file  = folder.createFile(ss);

    // BEGIN LOGGING FILE NAMES AND IDs
    var dropboxId = "SOME_FOLDER_ID_HERE" // Folder ID
    var theFolder = DriveApp.getFolderById(dropboxId); // Get folder by ID
    var list     = [];
    var theBlobs = [];
    if (totalFiles == finalCount) {
      var files = theFolder.getFiles();
      while (files.hasNext()) {
        var theFile = files.next();
        list.push(theFile.getId());
      }
      for (var i = 0; i < list.length; i++) {
        Logger.log(DriveApp.getFileById(list[i]).getName() + " : " + list[i]);
        theBlobs.push(DriveApp.getFileById(list[i]).getBlob());
      }
      Logger.log(theBlobs);
      // END LOGGING FILE NAMES AND IDs


      // BEGIN ZIPPING UP FILES
        var newZip = theFolder.createFile(Utilities.zip(theBlobs, 'zippedFiles.zip'));
        var zipId  = newZip.getId();
        Logger.log("Zip Id: " + zipId);
      // END ZIPPING UP FILES


      // BEGIN TRASHING UPLOADED FILES
      for (var i = 0; i < list.length; i++) {
        DriveApp.getFileById(list[i]).setTrashed(true);
      }
      // END TRASHING UPLOADED FILES
    }

    return file.getName();
  }catch(e){
    return 'Error: ' + e.toString();
  }
}

现在，它可以完美地用作附加到电子表格的脚本！

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于 2021-04-12

我来说两句

0 条评论

登录后参与评论

上一篇：ndigit的清晰说明[c-'0']

TOP 榜单

文章