google-apps-script - 用于复制和重命名文件夹结构的 Google App 脚本，包括更新文件夹中包含的文档和工作表中的字段？

Question

我必须首先道歉，因为我以前从未做过任何编码。我一直在做很多谷歌搜索并查看其他人的代码以尝试找出发生了什么。我有逻辑，但我不知道所有的类、触发器甚至是正确的术语！我可以在脑海中勾勒出需要发生的事情，但需要先学习语言，所以希望你能指出我学习材料的正确方向，并帮助完成这个特定的项目。

我想自动化我们在办公室经常做的事情，即创建一个新的客户文件夹，其中包含预定义的子文件夹，并获取模板文档和工作表，填写姓名和地址字段，并将它们与附加文档或工作表名称的客户名称到相关的新复制/创建的文件夹中。我正在努力连贯地解释这一点，所以我画了一些希望能有所帮助的东西： 带有文档和工作表位置的文件夹结构草图

我已经制作了一个将结果发布到电子表格的 Google 表单，并且我已经在该表格中放置了一个脚本。我已经弄清楚如何获取模板表和文档，将它们复制并重命名到一个文件夹中，但无法弄清楚我需要对这些文件夹做什么，以便将它们归档到驱动器上的唯一位置。

这是我正在测试复制模板、添加字段并重命名和保存的代码：

function autoFillGoogleDocFromForm(e) {
  var timestamp = e.values[0];
  var title = e.values[1];
  var firstName = e.values[2];
  var lastName = e.values[3];
  var emailAddress = e.values[4];
  var premisesType = e.values[6];

  
  //file is the template file, and you get it by ID
  var file = DriveApp.getFileById('1QjYas1erxtQjLPduNMoY1EIEAjBOI5qbAMeX59qPib4'); 
  
  //We can make a copy of the template, name it, and optionally tell it what folder to live in
  //file.makeCopy will return a Google Drive file object
  var folder = DriveApp.getFolderById('18GWERZFqgf9TbPfKDRX8gK02kbKpRLch')
  var copy = file.makeCopy(lastName + ', ' + firstName, folder); 
  
  //Once we've got the new file created, we need to open it as a document by using its ID
  var doc = DocumentApp.openById(copy.getId()); 
  
  //Since everything we need to change is in the body, we need to get that
  var body = doc.getBody();
  var header = doc.getHeader();
  
  //Then we call all of our replaceText methods
  header.replaceText('{{Premises type}}', premisesType);
  body.replaceText('{{First Name}}', firstName); 
  body.replaceText('{{Surname}}', lastName);  
  body.replaceText('{{Salutation}}', title); 
  body.replaceText('{{email}}', emailAddress);
  body.replaceText('{{Premises type}}', premisesType); 
  
  //Lastly we save and close the document to persist our changes
  doc.saveAndClose(); }

// REPLICATE ABOVE WITH SHEETS, fills out cell G2 with test info

function autoFillGoogleSheetFromForm(e) {
  var timestamp = e.values[0];
  var title = e.values[1];
  var firstName = e.values[2];
  var lastName = e.values[3];
  var emailAddress = e.values[4];
  var premisesType = e.values[6];

  
  //file is the template file, and you get it by ID
  var file = DriveApp.getFileById('1yvOtPcnGMQnE7Hc0d6vVMSG2Q5nY9u-MijaDZ51jlOU'); 
  
  //We can make a copy of the template, name it, and optionally tell it what folder to live in
  //file.makeCopy will return a Google Drive file object
  var folder = DriveApp.getFolderById('18GWERZFqgf9TbPfKDRX8gK02kbKpRLch')
  var copy = file.makeCopy(lastName + ', ' + firstName, folder); 
  
  //Once we've got the new file created, we need to open it as a document by using its ID
  var ss = SpreadsheetApp.openById(copy.getId()); 
  // ss is now the spreadsheet the script is associated with
  var sheet = ss.getSheets()[0]; 
  // sheets are counted starting from 0
  // sheet is the first worksheet in the spreadsheet
  var cell = sheet.getRange("G2"); 
  cell.setValue(premisesType);
}

希望有人能在不激怒我的情况下提供帮助。发这个帖子我太尴尬了！我保证我正在努力学习，而不是简单的选择！文斯。

编辑：感谢@iansedano，我已经编写了一些代码，这些代码让我得到了我需要的东西，除了在某些情况下，我多次创建了文件夹结构，就好像 smae 表单已经被重新提交了几次一样。这是我现在正在测试的代码：

function autoCreateFoldersSheetDoc(e) {
  var title = e.values[1];
  var firstName = e.values[2];
  var lastName = e.values[3];
  var emailAddress = e.values[4];
  var premisesType = e.values[6];

   // This is the root directory where all the client folders would be stored
  const customerRoot = DriveApp.getFolderById('1s4fD0wk8Mj_YiCkEOj7hUZedOv_AyPdl');
  const mainFolder = customerRoot.createFolder(lastName + ', ' + firstName);

   // Creating the sub folders
   // Some are assigned to variables so that later children can be added
  mainFolder.createFolder("1. Customer Correspondence")
  const costsFolder = mainFolder.createFolder("2. Costs")
  const proposalsFolder = mainFolder.createFolder("3. Proposals")
  mainFolder.createFolder("4. Drawings")
  mainFolder.createFolder("5. Testing & Commissioning")

   // Creating children
  costsFolder.createFolder("1. Suppliers")
  const dcaFolder = costsFolder.createFolder("2. DCA")

   // Take the template proposal doc and save a copy to the proposals folder
  var fileA = DriveApp.getFileById('1QjYas1erxtQjLPduNMoY1EIEAjBOI5qbAMeX59qPib4');
  var copyA = fileA.makeCopy(lastName + ', ' + firstName, proposalsFolder); 

   // Open new proposal doc and replace text
  var doc = DocumentApp.openById(copyA.getId());
  var body = doc.getBody();
  var header = doc.getHeader();
  header.replaceText('{{Premises type}}', premisesType);
  body.replaceText('{{First Name}}', firstName); 
  body.replaceText('{{Surname}}', lastName);  
  body.replaceText('{{Salutation}}', title); 
  body.replaceText('{{email}}', emailAddress);
  body.replaceText('{{Premises type}}', premisesType);
  doc.saveAndClose();

   // Take the template costs sheet and save a copy to the DCA folder
  var fileB = DriveApp.getFileById('1yvOtPcnGMQnE7Hc0d6vVMSG2Q5nY9u-MijaDZ51jlOU'); 
  var copyB = fileB.makeCopy(lastName + ', ' + firstName, dcaFolder); 

   // Open new costs sheet and replace text
  var ss = SpreadsheetApp.openById(copyB.getId()); 
  var sheet = ss.getSheets()[0]; 
  var cellA = sheet.getRange("G2"); 
  var cellB = sheet.getRange("H5");
  var cellC = sheet.getRange("H6");
  var cellD = sheet.getRange("H4");
  var cellE = sheet.getRange("I2");

  cellA.setValue(premisesType);
  cellB.setValue(firstName);
  cellC.setValue(lastName);
  cellD.setValue(title);
  cellE.setValue(emailAddress);

}

有什么想法可以防止重复吗？

标签： google-apps-scriptgoogle-drive-api

Answer 1

示例函数

/**
 * This function takes three arguments, the client name,
 * the costing sheet id, and the proposal doc id.
 * It will then create the folders and place the documents in the
 * right place, wherever they are in drive.
 */
function createClientAccountFolder(clientName, costingSheetId, ProposalDocId) {
  // This is the root directory where all the client folders would be stored
  const customerRoot = DriveApp.getFolderById('[YOUR_ROOT_FOLDER_ID]');

  if (clientName) {
    // This creates the main folder with the customer name
    const mainFolder = customerRoot.createFolder(clientName);

    // Creating the sub folders
    // Some are assigned to variables so that later children can be added
    mainFolder.createFolder("1. Customer Correspondence")
    const costsFolder = mainFolder.createFolder("2. Costs")
    const proposalsFolder = mainFolder.createFolder("3. Proposals")
    mainFolder.createFolder("4. Drawings")
    mainFolder.createFolder("4. Testing & Commissioning")

    // Creating children
    costsFolder.createFolder("1. Suppliers")
    const dcaFolder = costsFolder.createFolder("2. DCA")

    // Getting the documents
    const costingSheet = DriveApp.getFileById(costingSheetId)
    const proposalDoc = DriveApp.getFileById(ProposalDocId)

    // Moving the documents to the respective folders
    costingSheet.moveTo(dcaFolder)
    proposalDoc.moveTo(proposalsFolder)

  } else {
    Logger.log('No Client Name Specified')
  }
}

function test(){
  createClientAccountFolder("John Smith", "[SHEET_ID]", "[DOC_ID]")
}

我不确定如何将它准确地集成到您的其他脚本中，因为我没有能够测试它的数据。不过，作为建议，您可以在函数autoFillGoogleDocFromForm和autoFillGoogleSheetFromForm.

return id

您需要先获取文档的 id，然后将其分配给id变量。如果您想使用上面的函数而不做任何更改，请务必将其分配为字符串。

所以你需要做的就是从这样的函数中调用所有函数main：

function main(){
    const clientName = "John Smith"
    const docId = autoFillGoogleDocFromForm()
    const sheetId = autoFillGoogleSheetFromForm()
    createClientAccountFolder(clientName, sheetId, docId)
}

您可能还想要一种以clientName不同方式获得的方法，因此这一切都可以更容易地自动化。尽管希望您对需要完成的事情有所了解，并且可以根据需要对其进行修改。

让我知道是否有任何不清楚的地方！

参考

• 创建文件夹（名称）
• getFolderById(id)
• getFileById(id)
• 移动到（目的地）