App for CSA

Project Showcase
4

This is a script that I use to copy names from one sheet to 3 others. It’s a copied script that I found on the web and modified until it worked for me, so I don’t really have great details on how it works, but essentially I have a primary sheet with names. Whenever there is a new name added, the script will run automatically, check the 3 other sheets if the name exists, then add it to the next empty row if it does not already exists. It should work for sure as long as the value you want copied over is in the first column. I use the first 2 rows as header rows, so I only check values from row 3 and below and apply to rows 3 and below. That can be changed in the script to look at row 2 and below if you choose.

  // You can activate a trigger on function copy_new_names()
    // Assuming that row 1 and 2 in each sheet is not included in the values to be processed...

    function CopyName() {
    var SheetID = 'your spreadsheet ID here'
   // Enter the From sheet name and To sheet name)
    copy_new_names('LTS_Students', 'LTS_Tests', SheetID);
    copy_new_names('Advanced_Students', 'Moves_Tests', SheetID);
    copy_new_names('Advanced_Students', 'FreeStyle_Tests', SheetID);
    copy_new_names('Advanced_Students', 'Dance_Tests', SheetID);
    // copy_new_names('Advanced_Students', 'Add_Competition', SheetID);
    }    

    function copy_new_names(SheetFrom, SheetTo, SheetID) {
      var spreadsheet = SpreadsheetApp.openById(SheetID);
      var sheet = spreadsheet.getSheetByName(SheetFrom);
      var sheet2 = spreadsheet.getSheetByName(SheetTo);
      var endrow = sheet.getLastRow(), endcol = sheet.getLastColumn();
      var endrow2 = sheet2.getLastRow(), endcol2 = sheet2.getLastColumn();
      // assuming that row 1 and 2 is not included in the values to be processed
      var valuesSheet1 = sheet.getRange(3, 1, endrow-2, 1).getValues(), valuesSheet2 = [];
      if ( endrow2 > 2 ) valuesSheet2 = sheet2.getRange(3, 1, endrow2-2, 1).getValues();
      valuesSheet2 = [i for each (i in valuesSheet2)if ((i)[0].trim()!="")];

      var newlist = checkNameList(valuesSheet1, valuesSheet2);
      //Below to paste new list in sheet2 
      sheet2.getRange(3, 1, newlist.length, 1).setValues(newlist);
    }

    function checkNameList(valuesSheet1, valuesSheet2) {
      var values1 = valuesSheet1.map(function(d) { return d.join()}), values2 = [];
      if ( valuesSheet2.length ) values2 = valuesSheet2.map(function(d) { return d.join()});
      var newList = [];
        for ( var i in values1 ) {
          if ( values2.indexOf(values1[i]) === -1 && values1[i].length>0) newList.push([values1[i]]);
        }  
      newList = valuesSheet2.concat(newList);
      //Logger.log(newList)
      return newList;
    }