Populating multipls tabs from one tab in google-sheet

Ask for Help
1

I have a registration form that adds a new row into tab=Order_Form with Company Info and Contact info Email. Based on such received data (the system will generate a unique Client_Id).
I like to
Add a new row to tab=companies to add this new company info & tag it with this new Client_Id.
Add a new row to tab=contacts to add this new contact info & tag it with this new Client_Id.
Add a new row to tab=Users to activate this contact as a user & tag it with this new Client_Id.
Add 10 rows to tab=Apps & tag them also with this new Client_Id.

So, data from one tab into 4 tabs.
Glide doesn’t allow me to call a FORM from the main Form - else, I can build a wizard (at first time login).
Trying to stay away from Zapiers.

Any pointers!!

2

A Google apps script based on the Onchange trigger will be your friend, free of charge and not so complex :wink:

3

I do the exact same thing using a script.

4

Easy said than done for someone who never worked on google apps scripts :slight_smile:

Let me see if I can get creative with Arrayformulas and palce-holders - unless there is a ready-made script that I can simply learn how to tweak and plug in. (

5

My previous attached post includes the script I use. All it needs is a sheet id from your sheet, change the source and destination sheet tabs, and alter the beginning row and column to copy. In my case, my script reflects that the data I want to copy starts on row 3 and I am copying any new unique cell values in column A to column A in the other sheets tabs.

At the time of that post, I was copying unique student names to other sheets, which are accessed via relations in Glide. Now I copy RowIDs, but the concept is still the same.

1 Like
6

Thanks Jeff - my setup is a bit different, here’s a simplified presentation of what I’m trying to achieve.

image
image928Ă—529 12.6 KB

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
8

Many thanks Jeff - will follow such steps. Much appreciated.

1 Like