Have unique set of name without blank sheet name in array using app script

user_9613675 0
Viewed 39

I am trying to create a unique sheet name using an array format in the app script, this is the so far that I have come. The array has a blank sheet name also which is getting created as a new sheet every time the code runs along with a sheet that has a unique sheet name. I have runner the console log and found the array is capturing the blank sheet as well from the source spreadsheet name Image of error

function createSheet(){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceWs =ss.getSheetByName("PCR sheet");//This is the base sheet which has the data to capture and filter

const vendorName =sourceWs.
getRange(3,2,sourceWs.getLastRow()-2,1).
getValues().
map(vn =>vn[0]);

const uniqueVendorName = [ ...new Set(vendorName) ];//This is where the unique data is getting captured along with blank sheet name
console.log(uniqueVendorName);

const currentSheetNames = ss.getSheets().map( s => s.getName());

let ws;
uniqueVendorName.forEach(vendorName=> {
        if(!currentSheetNames.includes(vendorName)){
        ws = null;  
        ws = ss.insertSheet();
        ws.setName(vendorName);
        ws.getRange("A2").setFormula(`=FILTER('PCR Sheet'!A3:J,'PCR Sheet'!B3:B="${vendorName}")`); // This creates multiple sheet based on unique value on colum no 2 using pcr sheet.
        sourceWs.getRange("A2:J2").copyTo(ws.getRange("A1:J1"));//Header gets fixed for all pages
        
    
    } //if vendor name doesnot exist

}); //ForEach trough the list of vendor

}//close createsheet function

How to remove it and run the code without error.

google-apps-script google-sheets arrays javascript
1 Answers

I thought that in your script, the empty value is included in [ ...new Set(vendorName) ]. So, as a simple modification, how about the following modification?

From:

const uniqueVendorName = [ ...new Set(vendorName) ];

To:

const uniqueVendorName = [...new Set(vendorName)].filter(String);

Reference:

user_71086530
Related