Populating multipls tabs from one tab in google-sheet

Ask for Help
7

The concept would be the same. Instead of a single function to copy_new_names, you could probably create separate functions to handle each sheet you are adding to. I would also possibly make a duplicate of subscriber and date in between company name and contact email to the getrange in the script works. There are probably better ways to handle this, such as concatenating the 3 columns into one column with a delimiter (such as a pipe “|” or a comma “,”), writing that to the first column like my original script, then performing a split on the delimiter to fill the 3 columns. Shouldn’t really matter.

I modified my original script to hopefully fit your scenario. It’s not tested, so hopefully it works. Again, this assumes that you duplicate the subscriber and date columns after the company_name column.

` // 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 Sheet ID Here *****'
  if (SpreadsheetApp.getActiveSheet().getName() == "Contacts") {
    copy_companies('Contacts', 'Companies', SheetID);
    copy_users('Contacts', 'Users', SheetID);
  }
}    

function copy_companies(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 is not included in the values to be processed
  var valuesSheet1 = sheet.getRange(2, 1, endrow-1, 3).getValues(), valuesSheet2 = [];
  if ( endrow2 > 1 ) valuesSheet2 = sheet2.getRange(2, 1, endrow2-1, 3).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(2, 1, newlist.length, 3).setValues(newlist);
}

function copy_users(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 is not included in the values to be processed
  var valuesSheet1 = sheet.getRange(2, 4, endrow-1, 6).getValues(), valuesSheet2 = [];
  if ( endrow2 > 1 ) valuesSheet2 = sheet2.getRange(2, 1, endrow2-1, 3).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(2, 1, newlist.length, 3).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;
}`
4 Likes