首页 文章

如何使用不同的google工作表设置公式

提问于
浏览 次
0

我在主文件中的部分代码是在不同工作表中的特定单元格中传输或创建公式 .

处理：
1.触发后，它将获取当前电子表格中E1 [客户名称]中的值 . 它还将获取数据的最后一行以确定要复制的行的最大值 .

2.根据客户端名称查找文件夹名称，该文件夹具有标准名称"NEW" - [客户端名称] .

3.然后获取文件名或电子表格的ID，其中文件名包含"Client Worksheet"，有时命名为[客户端名称]：客户端工作表 .

4.然后它将打开文件并选择"Client Project" sheetname

5.并将单元格A1设置为类似具有importrange的查询的公式

这是我现有的代码：

function transferTasks() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var shtClient = ss.getSheetByName("SEO Deliverables Template");
      var cName = shtClient.getRange(1,5).getValue();
      var lastRow = shtClient.getLastRow();

    var folder = "NEW - " + cName;
    var folderID = getFolderID(folder); //get the folder ID of the client
    //the getfolderID function is successfull on getting the folder ID

    //get the file ID of the client worksheet file
      var cWorkSheet = DriveApp.getFolderById(folderID).searchFiles('title contains Client Worksheet');
        while (cWorkSheet.hasNext()) { //here is the error: invalid argument
          var file = cWorkSheet.next(); 
          var fileID = file.getId();
          }

  //transfer the data from SEO Deliverables Template to client folder template
  var sourceFile = ss.getSheetByName("Sydney Office Projects");
    var shtTargetFile = DriveApp.getFileById(fileID);
    var shtTarget = shtTargetFile.getSheetByName("Client Project Calendar");
    shtTarget.getRange(1,1).setValue="testing"


}

测试应该由代码替换：

=IFERROR(query(IMPORTRANGE("123456789","'sheet'!A2:I"), " select * where Col6 = 'clientname' label Col1 'Assigned Date'",1),"")

有关应该使用什么代码的任何帮助？

google-apps-script google-sheets

2 回答

  • 1

    setFormula现在正在工作：

    var formula2 = "=IFERROR(QUERY(IMPORTRANGE(\""+ shtClientID + "\",\"'Sydney Office Projects\'!A2:I\"), \" select * where Col6 = \'"+ cName +"\' label Col1 \'Assigned Date\'\",1),\"\")";
  shtTarget.getRange(1, 1).setFormula(formula2);
    回复于
  • 0

    它我现在正在工作，另外还有一件事就是运行的setFormula ...

    function transferTasks() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shtClient = ss.getSheetByName("SEO Deliverables Template");
  var shtClientID = shtClient.getID();
  var cName = shtClient.getRange(1,5).getValue();
  var lastRow = shtClient.getLastRow();

  var folder = "SEO - " + cName;
  var folderID = getFolderID(folder); //get the folder ID of the client
  var targetFolder = DriveApp.getFolderById(folderID);

  var file = "Client Worksheet";
  var files = DriveApp.getFolderById(folderID).searchFiles("title contains '" + file+"'"); 
   while (files.hasNext()) {
     var file = files.next();
     var fileID = file.getId();
     }

  var shtTargetFile = SpreadsheetApp.openById(fileID);
  var shtTarget = shtTargetFile.getSheetByName('Client Project Calendar');

  //var formula = '=IFERROR(QUERY(IMPORTRANGE('+ shtClientID + ',"\'sheet\'!A2:I"), " select * where Col6 = \'+ cName +\' label Col1 \'Assigned Date\'",1))'
  shtTarget.getRange(1, 1).setValue("testing"); //replace with the code below

shtTarget.getRange(1, 1).setFormula(formula); 
}
    回复于

相关问题