javascript - 带有选择框的 Google 脚本可从云端硬盘复制文件

Question

我想在我的 GSheet 中集成一个脚本以轻松实现一个选择框，该框允许用户突出显示并选择一个文件（从包含在精确文件夹中的文件列表中选择）并将其复制到原始 GSheet 中。在这里您可以找到选择框的示例：

在此处输入图像描述

我从 tanaikech 找到了一个非常实用且编写良好的脚本：https ://gist.github.com/tanaikech/96166a32e7781fee22da9e498b2289d0 还有 code.gs 和 index.html 文件的代码：

function main() {
  SpreadsheetApp.getUi().showSidebar(
    HtmlService
    .createTemplateFromFile('index')
    .evaluate()
    .setTitle("File selector sample")
  );
}

function getFiles(e) {
  var data = {};
  var idn = e;
  e = e == "root" ? "FOLDER ID HERE" : e;
  data[e] = {};
  data[e].keyname = DriveApp.getFolderById(e).getName();
  data[e].keyparent = idn == "root"
    ? null : DriveApp.getFolderById(e).getParents().hasNext()
    ? DriveApp.getFolderById(e).getParents().next().getId() : null;
  data[e].files = [];
  var da = idn == "root" ? DriveApp.getRootFolder() : DriveApp.getFolderById(e);
  var folders = da.getFolders();
  var files = da.getFiles();
  while (folders.hasNext()) {
    var folder = folders.next();
    data[e].files.push({name: folder.getName(), id: folder.getId(), mimeType: "folder"});
  }
  while (files.hasNext()) {
    var file = files.next();
    data[e].files.push({name: file.getName(), id: file.getId(), mimeType: file.getMimeType()});
  }
  return data;
}

function doSomething(id) {
  // do something
  var res = id;
  return res;
}

<style>
  #select_file {
    text-align: left;
    width: 95%;
    font-size: 1em;
    margin: auto;
    height: 3em;
  }
</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.js"></script>

<select id="select_file"></select>
<p id="id"></p>

<script>
var alldata = {};

$(function() {
  google.script.run.withSuccessHandler(importData).getFiles("root");

  $('#select_file').change(function() {
    var id = $('#select_file').val();
    var disp = $('#select_file option:selected').text();
    if (~disp.indexOf("Folder") || ~disp.indexOf("../")) {
      $('#select_file > option').remove();
      if (alldata[id]) {
        var dat = {};
        dat[id] = alldata[id];
        importData(dat);
        return;
      } else {
        google.script.run.withSuccessHandler(importData).getFiles("FOLDER ID HERE");
        return;
      }
      return;
    }
    google.script.run.withSuccessHandler(output).doSomething(id);
  });
});

function importData(e) {
  var key = Object.keys(e)[0];
  if (!alldata[key]) alldata[key] = e[key];
  if (e[key]["keyparent"]) {
    $('#select_file').append($('<option>').html("./" + e[key]["keyname"]).val(key));
    $('#select_file').append($('<option>').html("../").val(e[key]["keyparent"]));
  } else {
    $('#select_file').append($('<option>').html("./" + e[key]["keyname"]).val(key));
  }
  for (var i=0; i < e[key]["files"].length; i++) {
    $('#select_file').append($('<option>')
      .html(e[key]["files"][i].mimeType == "folder" ? "[Folder]" + e[key]["files"][i].name : e[key]["files"][i].name)
      .val(e[key]["files"][i].id)
    );
  }
}

function output(res){
  $('#id').text(res);
}
</script>

最后，我设法找到另一个脚本来捕获 Drive 文件夹中的文件，方法是指定它的 ID 以及我想在另一个具有不同 ID 的目标工作表中复制的文件：

function getData() {
  //declare multiple variables in one statement - Initial value will
  //be undefined
  var destinationSpreadsheet,destinationSS_ID,destsheet,destrange,file,files,
      sourcerange,sourceSS,sourcesheet,srcSheetName,sourcevalues;
 
  //inizio della macro
  srcSheetName = "NAME OF THE ORIGIN SHEET";
  destinationSS_ID = "ID OF THE DESTINATION SHEET";
  files = DriveApp.getFolderById("ORIGIN ID OF THE FOLDER").getFileById('THIS IS THE VARIABLE I WANT TO CATCH FROM THE SELECTION BOX BY THE USER');
  destinationSpreadsheet = SpreadsheetApp.openById(destinationSS_ID);
  destsheet = destinationSpreadsheet.getSheetByName('NAME OF THE DESTINATION SHEET');  

  while (files.hasNext()) {
    file = files.next();
    if (file.getMimeType() !== "application/vnd.google-apps.spreadsheet") {
      continue;
    };
    sourceSS = SpreadsheetApp.openById(file.getId());
    sourcesheet = sourceSS.getSheetByName(srcSheetName);
    //sourcesheet.getRange(start row, start column, numRows, number of Columns)
    sourcerange = sourcesheet.getRange(1,1,sourcesheet.getLastRow(),12);
    sourcevalues = sourcerange.getValues();

    //Write all the new data to the end of this data
    destrange = destinationSpreadsheet.getSheetByName("NAME OF THE DESTINATION SHEET")
        .getRange(1,1,sourcevalues.length,sourcevalues[0].length);

    destrange.setValues(sourcevalues);         
  };
  
  //select cell A2
  destsheet.getRange('A2').activate();
  
};

所以最后我的问题是：我可以通过将最后一个（函数 getData）集成到函数doSomething(id)中的第一个脚本中来组合这两个脚本吗？

非常感谢！

标签： javascriptgoogle-apps-scriptgoogle-sheets