Google App Script to copy rows into dynamically created tabs based on the value of a column

Question

First - I'm a teacher and self-teaching myself apps script, so please forgive my sloppy code. The results of my district's frequent standardized tests are often delivered with all the tests combined into one sheet. They would be much easier to manage if each test were in their own sheets.

I modified a function that I found on this site to create one new tab for each unique value in the test column (M). That takes care of having tabs to copy the rows into. It uses an array and I honestly don't understand it completely.

function createNewSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = spreadsheet.getSheetByName('Sheet1');
  var source_range = masterSheet.getRange("A1:AD1"); //header row

  // Retrieve 2d array for column M
  var colA = masterSheet.getRange('M2:M').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); //inserts new sheet
      source_range.copyTo(spreadsheet.getSheetByName(sheetName).getRange("A1:AD1")); //writes header row to newly created sheet
    }
  });
}

In order to get the correct rows into the corresponding tabs I ended up using else if statements. However, it means that I have to anticipate all the different tabs that will be created by the first function and then modify my code before running it. It's also quite slow, but tolerable. While this works there has to be a better way. If anyone has any suggestions I'd really appreciate it.

function copyRows() {
  var sSheet = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = sSheet.getSheetByName("Sheet1"); 
  var lastRow = srcSheet.getLastRow();

  for (var i = 2; i <= lastRow; i++) {
    var srcRange = srcSheet.getRange("A" + i + ":AD" + i);
    var cell = srcSheet.getRange("M" + i);
    var val = cell.getValue();

  //sets the target sheet depending on the exam in column M

    if (val == "Growth: Language 2-12 KS 2017") {
      var tarSheet = sSheet.getSheetByName("Growth: Language 2-12 KS 2017");
    }

    else if (val == "Growth: Math 2-5 KS 2017") {
      var tarSheet = sSheet.getSheetByName("Growth: Math 2-5 KS 2017");
    }  

    else if (val == "Growth: Reading 2-5 KS 2017") {
      var tarSheet = sSheet.getSheetByName("Growth: Reading 2-5 KS 2017");
    } 

    else if (val == "Growth: Reading K-2 KS 2017") {
      var tarSheet = sSheet.getSheetByName("Growth: Reading 2-5 KS 2017");
    } 

    else if (val == "Growth: Math K-2 KS 2017") {
      var tarSheet = sSheet.getSheetByName("Growth: Reading 2-5 KS 2017");
    } 

    else {
    }
  //insets the row in the correct target worksheet  
      var tarRow = tarSheet.getLastRow()+1;
      tarSheet.insertRows(tarRow);
      var tarRange = tarSheet.getRange("A" + (tarRow) + ":AD" + (tarRow));
      srcRange.copyTo(tarRange);    
  }
};

Here is a link to a sheet with dummy data. My actual sheets can have upwards of 1500 to 2000 rows with 8 or more tests.

Answer 1

If I understand you correctly, you want a function that:

• Creates a new sheet for each different Assessment Name (column M from Sheet1).
• Appends the rows corresponding to each Assessment Name to the corresponding created sheet.

If that's the case, then you could iterate through all data in Sheet1, and for each row, check if there is a sheet in the spreadsheet whose name equals the corresponding assessment name.

If such a sheet it exists, the script appends the current row to the end of the target sheet.

If the sheet doesn't exist, it first creates that sheet with the headers on top, and then appends the current row.

This is accomplished by the use of appendRow(rowContents).

Your code could be something along the following lines:

function createNewSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = spreadsheet.getSheetByName('Sheet1');
  // Get range with data from original sheet:
  var firstRow = 1;
  var firstCol = 1;
  var numRows = masterSheet.getLastRow();
  var numCols = masterSheet.getLastColumn();
  var data_range = masterSheet.getRange(firstRow, firstCol, numRows, numCols).getValues();
  // Get headers row (to be appended on top when new sheet is created):
  var headers = data_range[0];
  // Iterate through all rows of data from Sheet1:
  for (var i = 1; i < data_range.length; i++) {
    var row = data_range[i]; // Current row
    var assessmentName = row[12]; // Assessment name
    // Get sheet with current assessment name:
    var sheet = spreadsheet.getSheetByName(assessmentName);
    // Check if sheet with current assessment name exists. If it doesn't it creates it:
    if (!sheet) {
      sheet = spreadsheet.insertSheet(assessmentName);
      sheet.appendRow(headers);
    }
    // Appends current row of data to the new sheet:
    sheet.appendRow(row);
  }
}

Additionally, if you want to avoid duplicating the rows appended every time the script runs, you could have a column in the last column of your main sheet that keeps track of which rows have been copied to the new sheet, and if they have been copied, avoid appending them again. You could, for example, have a column header in AC called Copied. Then, when the row is appended, that row is populated with, for example, the string "Done". Next time the script runs, it can check whether the corresponding cell has the value "Done".

You could, for example, change the end of the function, from this:

    // Appends current row of data to the new sheet:
    sheet.appendRow(row);
  }
}

To this:

    // Appends current row of data to the new sheet if `AC` column value is not "Done":
    if (row[28] != "Done") {
      sheet.appendRow(row);
      masterSheet.getRange(i + 1, 29).setValue("Done");    
    }
  }
}

I hope this is of any help.

Answer 2

This function could replace your copy rows script and it should run considerably faster.

function copySheets() {
  var keyA=["Growth: Language 2-12 KS 2017", "Growth: Math 2-5 KS 2017", "Growth: Reading 2-5 KS 2017", "Growth: Reading K-2 KS 2017", "Growth: Math K-2 KS 2017"];
  var shtA=["Growth: Language 2-12 KS 2017", "Growth: Math 2-5 KS 2017", "Growth: Reading 2-5 KS 2017", "Growth: Reading K-2 KS 2017", "Growth: Math K-2 KS 2017"];
/*
I made the above arrays to different one in the event that you ever wish to change the mapping.

The code below gets all of the data on the source sheet from column1 to column 30 and row 2 to the last row.  Whenever you have a startrow that is not 1 you need to subtracting off the rows above the start row from the sheet.getLastRow() because the third parameter for get range is not the last row but the number of rows.

This code get all of the data in one two dimensional array and sticks it into the target range all at one time.  In my experience you should see a 10x increase in performance.
*/
  var ss=SpreadsheetApp.getActive();
  var srcsh=ss.getSheetByName("Sheet1"); 
  var vA=srcsh.getRange(2,1,srcsh.getLastRow()-1,30).getValues();
  var keyToSheet={};
  keyA.forEach(function(key,i){keyToSheet[key]=shtA[i];})
  var tarsh=ss.getSheetByName(keyToSheet(val));
  var tarrg=tarsh.getRange(2,1,vA.length,vA[0].length).setValues(vA);
}