# Copying users from referred table to userprofile table
**URL:** https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883
**Category:** Ask for Help
**Created:** [July 21, 2020, 1:43pm UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883 "2020-07-21T13:43:18Z")
**Posts on this page:** 14
**Page:** 1
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [July 21, 2020, 1:43pm UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883/1 "2020-07-21T13:43:18Z")
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
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [July 21, 2020, 1:57pm UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883/2 "2020-07-21T13:57:39Z")
Are you doing in this in an app with user profiles settings?
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [July 21, 2020, 1:58pm UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883/3 "2020-07-21T13:58:32Z")
Yes using a form to collect referrals
App is whitelisted
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [July 21, 2020, 4:15pm UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883/4 "2020-07-21T16:15:23Z")
I have made a new script for you. Here’s a quick demo with the 2 sheets.
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [July 21, 2020, 4:18pm UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883/5 "2020-07-21T16:18:20Z")
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”.
> **[ThinhDinh's Glide Lab](https://docs.google.com/spreadsheets/d/1sEQHpSKyHx9w2F_Xx3lm56SU1p-8eI81OYU5TYvQFvw/edit#gid=1275968609)**
>
> Email choices
>
> Packs,Packs difference,Duration,🔒Row...
---
### Author:  [@Glider](https://community.glideapps.com/u/Glider)
#### Post date: [July 21, 2020, 4:31pm UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883/6 "2020-07-21T16:31:21Z")
thanks a lot @ThinhDinh i am surely going to use this
👏👏👏👏👏👏👏👏 👏👏👏👏👏
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [July 22, 2020, 7:26am UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883/7 "2020-07-22T07:26:29Z")
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
> **[addusers](https://docs.google.com/spreadsheets/d/1pKRby8CElbw5OjsAN1jgzs_MGnQh1sjbuAGz-E41n98/edit?usp=sharing)**
>
> Referrals
>
> Referral email,Existed?,Copied?
> rob@gmail
> fred@gmail.com
> me@hotmail.com
> thin@gmail.com
> sdfsdf@gmail.com
> xfdfasdf@gmail.com
Many thanks for your help
Rob
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [July 22, 2020, 8:00am UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883/8 "2020-07-22T08:00:18Z")
Hi Rob, can you share edit access to [ariesarsenal@gmail.com](mailto:ariesarsenal@gmail.com)? Thank you.
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [July 22, 2020, 9:34am UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883/9 "2020-07-22T09:34:07Z")
Shared sheet with you
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [July 22, 2020, 9:42am UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883/10 "2020-07-22T09:42:52Z")
You forgot to copy the ARRAYFORMULA in the “Existed?” column over. I have made it work as expected. Please check again.
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [July 22, 2020, 9:45am UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883/11 "2020-07-22T09:45:17Z")
ThinhDinh,
Many thanks much appreciated
Rob
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [July 22, 2020, 2:35pm UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883/12 "2020-07-22T14:35:39Z")
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
> **[Dragon Society Martial Science](https://docs.google.com/spreadsheets/d/1ABuw1SWvOyW633eIlcI41INMV_zFbKOuRcMArCjWEVE/edit?usp=sharing)**
>
> Techniques
>
> Techniques,Basic Science,Point 1,Point 2,Point 3,Point 4,Point 5,Point 6,Point 7,Point 8,Point 9,Point 10,Mastery Concepts/Avoiding Pitfalls,Image,Gif,Video,Carousel,TechImage1,TechImage2,TechImage3
> Technique 1,Fire burns...
Thankyou
Rob
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [July 22, 2020, 2:59pm UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883/13 "2020-07-22T14:59:40Z")
I have modified the script a bit, seems like the arrayformulas made the getLastRow works the wrong way. Should work now.
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [July 22, 2020, 3:17pm UTC](https://community.glideapps.com/t/copying-users-from-referred-table-to-userprofile-table/12883/14 "2020-07-22T15:17:42Z")
ThinhDinh,
Many thanks for all your time on this.
It is all working thank you very much appreciated
Rob