Copying users from referred table to userprofile table

Ask for Help
1

Hi Guys
Can anyone help as I am new to Google Apps Scripts and would like to be able to do the following via apps script. I want to be able to do the update based upon a new entry being added to the ReferredUsers sheet
I have a table (ReferredUsers) with referred users email addresses in it column ‘B’
I want to be able to copy the referred users email address and add it to the UserProfiles sheet Column ‘C’ if it does not already exist.

Many Thanks

Rob

2

Are you doing in this in an app with user profiles settings?

3

Yes using a form to collect referrals
App is whitelisted

4

I have made a new script for you. Here’s a quick demo with the 2 sheets.

5

The script is here, copy it to your sheet, and add an “On change” trigger for it:

function copyReferrals(e) {
   var Active = e.source.getActiveSheet().getName();
if(Active == 'Referrals'){
   
   Utilities.sleep(1000);
   //setup function
   var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Referrals");
   var StartRow = 2;
   var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
   var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,3);
   var AllValues = WholeRange.getValues();
  
   //iterate loop
   for (i in AllValues) {
  
   //set current row
   var CurrentRow = AllValues[i];
  
   //define column to check if sent (starts from "0" not "1")
   var Existed = CurrentRow[1];
   var Copied = CurrentRow[2];
  
   //if row has been sent, then continue to next iteration
   if (Copied == "Copied" || Existed == "Existed" || Existed == "") 
   continue;
   
//set the row to look at
var setRow = parseInt(i) + StartRow;
  
//mark row as "sent"
ActiveSheet.getRange(setRow, 3).setValue("Copied");

var CopySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("User email");
var Target = CopySheet.getRange(CopySheet.getLastRow()+1,1);
ActiveSheet.getRange(setRow, 1, 1, 1).copyTo(Target);

  }
}
}

Basically the steps are below:

  • Check if the event comes from the ‘Referrals’ sheet. If yes, proceed. If not, stop.
  • Get all rows with data in the ‘Referrals’ sheet, only proceed to copy over the email values that have not been copied, or does not exist in the email (validated using an Arrayformula).
  • After the copying process, the “Copied” value is added to the row, the validation becomes “Existed”.
1 Like
6

thanks a lot @ThinhDinh i am surely going to use this
:clap::clap::clap::clap::clap::clap::clap::clap: :clap::clap::clap::clap::clap:

1 Like
7

ThinhDinh,
Thankyou for taking the time to do this much appreciated.
I must be doing something wrong as I have copied your code even having using the same names for the sheet tabs, but it doesn’t do anything ?
The trigger executes 100% no errors
Help ? What have I missed please ?

Here is a link to my sheet

Many thanks for your help

Rob

8

Hi Rob, can you share edit access to ariesarsenal@gmail.com? Thank you.

9

Shared sheet with you

10

You forgot to copy the ARRAYFORMULA in the “Existed?” column over. I have made it work as expected. Please check again.

11

ThinhDinh,
Many thanks much appreciated

Rob

1 Like
12

ThinhDinh,
Sorry to be a nuisance.
I n a test worksbook it works fine
.
However as soon as I copy it to my live app workbook it doesn’t work correctly
When I add a new email to the Referrals sheet It flags as ‘Not existed’ , then says Copied, but nothing has been copied, and the Existed? status stays as ‘Not existed’.

Looking at the trigger execution failures I get the following message
" Error The coordinates of the target range are outside the dimensions of the sheet."

I have shared the sheet to you in anticipation

Thankyou

Rob

13

I have modified the script a bit, seems like the arrayformulas made the getLastRow works the wrong way. Should work now.

14

ThinhDinh,
Many thanks for all your time on this.
It is all working thank you very much appreciated

Rob

1 Like