google-apps-script - Google Script in Sheets, Reference 最后一行，找到票号和客户号，然后用它找到文件夹添加子文件夹

Question

我希望我能清楚地解释这一点。

我有一个带有“票证”表和“客户数据”表的 Googlesheet。工单有 A 列作为“票号”，B 列作为“客户姓名” 客户数据表有 A 列客户编号，C 列“名字”，D 列“姓氏”。

我有一个脚本，当每个客户添加到新行时，它会在驱动器中的文件夹中创建一个文件夹，格式为“姓氏名字 - 客户编号”

我想要做的是将新行添加到“Ticket”表中，脚本可以找到客户文件夹，然后添加名为“TicketNumber”的子文件夹。

这是我一直在尝试的代码，因此您可以看到我卡在哪里。谢谢

    //Global

var googleFolderID = ('DRIVE FOLDER ID');
var ss = SpreadsheetApp.getActive();

function createTicketFolder() {


// identify the sheet where the data resides
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("Tickets");

//Select the column we will check for the first blank cell
var columnToCheck = sheet.getRange("A:B").getValues();

// Get the last row based on the data range of a single column.
var lastRow = getLastRowSpecial(columnToCheck);

  //identify the cell that will be used to name the folder 
 var ticketNumber = sheet.getRange(lastRow,1,1,1);
  var customerNumber = sheet.getRange(lastRow,2,1,1);


//identify the parent folder the new folder will be in
var parentFolder=DriveApp.getFolderById(googleFolderID);

// Find Customer folder within parentFolder, formatted as "Lastname Firstname - CustomerNumber"
//Need Help here

//create the new folder
var newFolder=customerFolder.createFolder(ticketNumber);


};



function getLastRowSpecial(range){
  var rowNum = 0;
  var blank = false;
  for(var row = 0; row < range.length; row++){

    if(range[row][0] === "" && !blank){
      rowNum = row;
      blank = true;
    }else if(range[row][0] !== ""){
      blank = false;
    };
  };
  return rowNum;
};

Google 表格 “票证”表格

“客户数据”表

Google Drive 文件夹 这是 google drive 文件夹的格式化方式。

因此，我需要从上面的表格中找到客户文件夹并添加一个以票号作为文件夹名称的子文件夹。

标签： google-apps-scriptgoogle-sheets

Answer 1

您需要执行以下步骤

1. Tickets从工作表的最后一个条目中检索客户编号
2. 使用indexOf()Customer Data在工作表中查找包含此数字的行
3. 检索名字和姓氏以重建客户文件夹的名称
4. 按名称查找文件夹

样本：

...
//IMPORTANT: retrieve the value!
  var ticketNumber = sheet.getRange(lastRow,1).getValue();
  var customerNumber = sheet.getRange(lastRow,2).getValue();  
  //identify the parent folder the new folder will be in
  var parentFolder=DriveApp.getFolderById(googleFolderID);
  var customerSheet = ss.getSheetByName("Customer Data");
  var data = customerSheet.getDataRange().getValues();
  // the following line retrieves all customerIds from the 2-D value range and saves them in the 1-D array "customerIds" - necessary for using indexOf()
  var customerIds = data.map(function(e){return e[0];}); 
  var index = customerIds.indexOf(customerNumber);
  // if the customer number has been found
  if(index >= 0){
    // Lastname Firstname - Customernumber"
    var folderName = data[index][3] + " " + data[index][2] + " - " + data[index][0]; 
    //  retrieve the first folder with this name - important: avoid duplicates!
    var customerFolder = parentFolder.getFoldersByName(folderName);//.getFoldersByName(folderName);
    if(customerFolder.hasNext()){
      customerFolder = customerFolder.next();
      //check if ticket folder exists
      var ticketFolder=customerFolder.getFoldersByName(ticketNumber);
      if(ticketFolder.hasNext()){
        var newFolder = ticketFolder.next();
       } else {
        // if it does not exist, create the new folder
        var newFolder=customerFolder.createFolder(ticketNumber);
       }
    }    
  }