CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
u4carson
u4carson

Reputation: 35

Split One Google Sheet into Multiple Sheets based on Column Value - Replace Duplicate Sheets

This is getting to be above my level of knowledge and I was hoping for assistance. The script below works with some limits. This script checks to see if a region tab exists, if it doesn't, the regional data from the source worksheet is copied to a new tab by the name of that region. Region is column 24 on the source worksheet, the data starts on row 3 and the header is Row 2.

If the region tab already exists I would like for it to be deleted recreated or repopulated with current data instead of being skipped over.

function createSheets(){

const ss = SpreadsheetApp.getActiveSpreadsheet()
const sourceWS = ss.getSheetByName("Forecast (SQL) Validation")

const regions = sourceWS
  .getRange(3,24,sourceWS.getLastRow()-2,1)
  .getValues()
  .map(rng => rng[0])

const uniqueRegion = [ ...new Set(regions) ]

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

let ws

uniqueRegion.forEach(region => {

  if(!currentSheetNames.includes(region)){

    ws = null
    ws = ss.insertSheet()
    ws.setName(region)
    ws.getRange("A2").setFormula(`=FILTER('Forecast (SQL) Validation'!A3:CR,'Forecast (SQL) Validation'!X3:X="${region}")`)
    sourceWS.getRange("A2:CR2").copyTo(ws.getRange("A1:CR1"))

}//If regions doesn't exist

})//forEach loop through the list of region

} //close createsheets functions

Upvotes: 2

Views: 776

Answers (1)

Cooper
Cooper

Reputation: 64100

Try it this way

function createSheets() {
  const ss = SpreadsheetApp.getActive()
  const ssh = ss.getSheetByName("Forecast (SQL) Validation");
  const regions = ssh.getRange(3, 24, ssh.getLastRow() - 2, 1).getValues().flat();
  const urA = [...new Set(regions)];
  const shnames = ss.getSheets().map(s => s.getName())
  let ws;
  urA.forEach(region => {
    let idx = shnames.indexOf(region);
    if (~idx) {
      ss.deleteSheet(ss.getSheetByName(shnames(idx)));//if it does exist delete it and create a new one
    }//if it does not exist the just create a new one
    ws = null;
    ws = ss.insertSheet(region);
    ws.getRange("A2").setFormula(`=FILTER('Forecast (SQL) Validation'!A3:CR,'Forecast (SQL) Validation'!X3:X="${region}")`)
    ssh.getRange("A2:CR2").copyTo(ws.getRange("A1:CR1"))
  })
}

Explanation:

Loop through all tabs, and if the tab already exists, delete it via deleteSheet before creating it again, as you are doing with the non-existent ones..

Upvotes: 3

Related Questions