Google Sheets Create new tab

Question

Need some advise on Google Sheets.

Sadly one of our clients uses Google Sheets as their Excel fix. As part of this one of the staff spends hours moving data about.

The first part of the problem is that we have a tab called master. In column A is a variable amount of cells (Some duplicates). We want to be able to create a new tab based on the distinct value in the cell (So one sheet per distinct value)

Now in Microsoft Excel VBA I can write this with my eyes closed, but on Google sheets I have no idea.

Any help is appreciated.

Bardy Bardy · Accepted Answer · 2016-12-06T20:02:20

How about:

function createNewSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = spreadsheet.getSheetByName('master');

  // Retrieve 2d array for column A
  var colA = masterSheet.getRange('A:A').getValues();

  // Create a 1d array of unique values
  var uniqueValues = {};
  colA.forEach(function(row) {
    row[0] ? uniqueValues[row[0]] = true : null;
  });
  var newSheetNames = Object.keys(uniqueValues);

  newSheetNames.forEach(function(sheetName) {
    // Check to see whether the sheet already exists
    var sheet = spreadsheet.getSheetByName(sheetName);
    if (!sheet) {
      spreadsheet.insertSheet(sheetName);
    }
  });
}

Google Sheets Create new tab

1 Answers