new here to posting questions and new to using Apps Script for Google Sheets.

I'm trying to have a macro create a new sheet, based off of a template sheet that begins with "~". The macro should find anything in column A of another sheet that begins with a "Y". If it has a "Y", it should grab the vendor name value on the same row, but column B. Then this is used to create the new worksheet and name for it.

The code below executes, but nothing happens. I was able to get the template sheet to duplicate, but adding in the search in the vendor sheet tab seems to have caused an issue. Any help is appreciated. I also apologize in advance, first time poster so not totally sure this all makes sense.

    function DuplicateSht() {

var Sect = 0 
var Vend = 0 
var sh = SpreadsheetApp.getActiveSpreadsheet()  
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); 
var data = sh.getDataRange().getValues(); //
  for (var i=0 ; i<sheets.length ; i  ) {
    SpreadsheetApp.setActiveSheet(sheets[i])
          if (sheets[i].getName().startsWith("Vendor Sheet")){//locates sheet with base information
            Sect = sh.getRange("C2").getValue();//pulls in section number
            Vend = sh.getRange("C3").getValue();//pulls in number of vendors
            for (var j=10 ; j<Vend 10; j  ){
              if(data[j][1] == "Y"){//Looks for each vendor with a "Y" in column A.  The number of vendors to review is variable
                var VendName = sh.getRange("B"   j).getValue();//If a vendor has a "Y" will grab their name
                for (var k=0 ; k<sheets.length ; k  ) {
                  SpreadsheetApp.setActiveSheet(sheets[k])
                    for (var l=0 ; l<Vend ; l  ){
                      if (sheets[l].getName().startsWith("~")){
                        sh.duplicateActiveSheet()//duplicates the sheet beginning with "~"
                        sh.renameActiveSheet("x-"   VendName   " "   Sect)//renames the sheet based on the data from the prep template sheet, including vendor name
                      }
                     }
                 }
                }
              }
            }
    }
}

CodePudding user response：

My understanding of your challenge is that you have a spreadsheet with information on various vendors. The vendors are all listed on one sheet. Then each vendor, when they get a "Y" in one of the columns on that listing sheet, gets a dedicated sheet created based on a template.

If this structure is correct, then you can avoid the nested statements in your original code and be a little more direct around finding the vendors and creating the sheets.

Here's the spreadsheet I created that demonstrates this and shows the following code in action.

function DuplicateSht() {
  //since there is only one Vendor Sheet we can just set a variable pointing to it, then get the data in the sheet using getDataRange
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var ws = ss.getSheetByName("Vendor Sheet")
  var data = ws.getDataRange().getValues()

  //this "reduce function" filters the data so we have an array ("vendors") that only includes those rows with a "Y" in the first column 
  var vendors = data.reduce(function(acc, curr) {   
    if(curr[0] === "Y") {
      acc.push(curr)
    }
    return acc
  },[])

  //now loop through the vendors list and for each one set up the desired sheet name -- before duplicating the template, make sure it doesn't already exist. 
  for (i=0; i<vendors.length; i  ){
    var sect = vendors[i][2]
    var name = "x-"   vendors[i][1]   " "   sect

    //get the names of all the sheets
    var sheets = ss.getSheets().map(function (r){return r.getName()})  

    //check to see if the current vendor you are evaluating has already had a sheet created for it. If not, then create a new sheet based on the template
    if (sheets.indexOf(name) === -1){  
      ss.setActiveSheet(ss.getSheetByName("~Template"))
      ss.duplicateActiveSheet()
      ss.renameActiveSheet(name)
    } 
  }
}