seo Question posted in
Some basic information to get you started with this can be found here.

How to set formula using a different google sheet – SEO

Part of my code in the main file is to transfer or create a formula in a specific cell in a different sheet.

Process:

1. Once triggered, it will get the value in E1 [client name] in the current spreadsheet. it will also get the last row of the data to determine the maximum of rows to be copied.

2. Find it’s folder name based on the client name, the folder has standard name “NEW” – [client name].

3. Then get the file id or the id of the spreadsheet where it’s filename contains “Client Worksheet”, sometimes named as [client name]: Client Worksheet.

4. Then it will open the file and select “Client Project” sheetname

5. And will set the cell A1 to a formula like a query with importrange

Here is my existing code:

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"


}

testing is should be replaced by a code here:

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

Any help on what code should be used?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    it i now working, one more thing for this to run is the setFormula at the.. 

    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); 
}

  2. 0

    setFormula is now working:

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search