Google-sheets – Split Google Sheet into new Google Sheets by row key

google sheets

I have a sheet that has has a bunch of data in it, all tied to a site URL. It's not sorted data, and each site can have no log entries or many.

There are many items in this file, so I'm looking for a way to split the Sheet into multiple Sheets (not tabs) that have only content from one site. For example, one file would only have rows containing Team1 URLs, and the second would only contain Team2 URLs.

There are similar questions on here, but I'll have a large number of these files with a large number of different sites in each one. Doing an importrange on every URL as suggested here would be too time-consuming. This answer wouldn't work for the same reason.

I have a similar script that splits a sheet into a new tabs at a set line mark – eg, 25 items, but I'm not sure how to make this general enough to make and track on new Sheets.

Best Answer

I wrote some code to fix this as there wasn't any other solution, I'm not sure how elegant it is but it seems to work. I added some code that blacklists certain error types we don't want to see that wasn't part of the original question.

// press the run button when the sheet you want to split is selected.
function splitMeIntoPieces() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Transaction Log");
  var data = sheet.getDataRange().getValues();
  const sourceTypeBlacklist = ["SPRoleAssignment","SPRoleAssignmentSet", "SPDocumentVersion"]; // filter these out, we don't need them
  const headers = data.splice(0, 1); //seperate out the headers and store them

  const distinctSites = data.reduce((acc, row) => { //iterates over each object in the array https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/reduce
    const groupKey = row[1]; //selecting the second (B) colum as the key - what we want to group by
    if (!acc[groupKey]) { //if the accumulator (the output object) does not have the key, set an empty one. we'll fill it in in the next step
      acc[groupKey] = [];
    }
    if (sourceTypeBlacklist.indexOf(row[9])==-1 ) { //ignore permissions errors
      acc[groupKey].push(row); // add the row to the array that matches the group key
    }
    return acc; //the accumulator object now has the previous data and is passed again to work on the next row
  }, {});

  for (var x in distinctSites) { 
    const rowsForSite = distinctSites[x];
    // "For each individual site in this list"
    var newss = SpreadsheetApp.create(("Logs for the Teams site: "+x));
    //console.log("new sheet is:" + newss.getId());
    newss.setActiveSheet(newss.getSheets()[0]);
    const rowsToWrite = headers.concat(rowsForSite);

    var range = newss.getRange("A1:Q"+ (rowsToWrite.length)); //remeber we're writing the headers along with the data

    //console.log("length: "+rowsForSite.length);
    //console.log("Working on site: ",x);

    for (var q in rowsForSite) {
      // this is each log item for each site in the list
      const row = rowsForSite[q];
      //console.log(row)
    }

    range.setValues(rowsToWrite);
  }
}
Related Topic