# Help with App Script Data manipulation in Arrays
**URL:** https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669
**Category:** Ask for Help
**Created:** [June 11, 2020, 10:53am UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669 "2020-06-11T10:53:31Z")
**Posts on this page:** 19
**Page:** 1
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [June 11, 2020, 10:53am UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/1 "2020-06-11T10:53:31Z")
Hi guys
I would appreciate some help please
I am working on an app and am having difficulty in taking individual cell information from one sheet “APP: Sales” and updating a row and specific cells in another sheet based upon the users email address.
But am struggling to get my head around the app script code to manipulate the data in arrays ?
The data I need from the “APP: Sales” sheet is the ‘Item SKU#’ and the ‘Paid at’ cells
these need to match the email address with the email address in the “AuthUsers” sheet
and then update the columns for each of the modules purchased for that user.
This update needs to happen everytime a purchase occurs.
In my example sheet I have included an exmaple of the desired output.
My example spreadsheet is accessible via this link
[[Example - Google Sheets](https://docs.google.com/spreadsheets/d/1ZeGWKsEFNTbPO8ZcEdG9Izm5QmE2l1GkY8wWVxcv9TM/edit?usp=sharing)]
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [June 11, 2020, 11:46am UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/2 "2020-06-11T11:46:41Z")
Hi Robert,
I have made all the needed changes and it should update automatically now.
Make sure you name the SKUs exactly like the corresponding columns in the result sheet.
Best regards.
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [June 11, 2020, 11:57am UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/3 "2020-06-11T11:57:15Z")
ThinhDinh,
Thankyou for doing this much appreciated.
I do have a question in the sample sheet cell A3 in AuthUsers has an error ?
#REF! - **Error** Array result was not expanded because it would overwrite data in A8.
what is the implication of this please?
Many thanks
Rob
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [June 11, 2020, 11:58am UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/4 "2020-06-11T11:58:37Z")
I made it automatically update so instead of having “fred” there, try inserting a record in App: Sales!
You’re trying to overwrite an arrayformula, hence it returns an error.
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [June 11, 2020, 12:00pm UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/5 "2020-06-11T12:00:25Z")
sorry my mistake
again many thanks for your help
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [June 11, 2020, 12:04pm UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/6 "2020-06-11T12:04:22Z")
Hey no worries Robert, if you need any further help feel free to comment here.
Stay safe during these times as well, have a nice day!
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [June 11, 2020, 12:09pm UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/7 "2020-06-11T12:09:50Z")
You too thanks again
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [June 11, 2020, 12:14pm UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/8 "2020-06-11T12:14:52Z")
ThinhDinh,
Sorry to be a pain, but I have just realized that the user always gets originally added to AuthUsers before any sale is made.
This is because AuthUsers is the email whitelisting and a user may not purchase anything but just use the free elements of the app so will never appear in APP: Sales
But will always appear in AuthUsers first
is there a way around that please?
Thanks
Rob
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [June 11, 2020, 1:58pm UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/9 "2020-06-11T13:58:11Z")
Hi, I have just removed the arrayformula from the email and the name column. You’re good to go now.
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [June 11, 2020, 2:52pm UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/10 "2020-06-11T14:52:03Z")
ThinhDinh,
That works fine in the example sheet thankyou.
However when I copy it over to the live sheet it doesn’t do the population of data at all.
If I manually fill the date field in Authusers then the Module value “Techniques” etc gets populated fine it seems as though it is not doing the Vlookup for some reason?
Regards
Rob
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [June 11, 2020, 2:56pm UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/11 "2020-06-11T14:56:33Z")
If you’re ok with it you can temporarily share your main sheet to [ariesarsenal@gmail.com](mailto:ariesarsenal@gmail.com). I’ll have a look.
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [June 11, 2020, 3:01pm UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/12 "2020-06-11T15:01:44Z")
ThinhDinh,
Thankyou will do the only two sheets relevant for this are
AuthUsers
App: Sales
shared now
Many Thanks
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [June 11, 2020, 3:08pm UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/13 "2020-06-11T15:08:38Z")
It should work as normal now. You forgot to change the VLOOKUP range (it was C3:C, it should be C10:C now because you are now starting it at row 10).
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [June 11, 2020, 3:10pm UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/14 "2020-06-11T15:10:17Z")
ThinhDinh,
Thankyou really appreciate all your help and patience
Thanks again
Rob
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [June 11, 2020, 5:16pm UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/15 "2020-06-11T17:16:33Z")
Hi ThinhDinh,
I have a question how would you modify the vlookup filter to ensure that only transactions that are less than a year old update the AuthUsers sheet.
Also do all entries get updated on every run or just the new rows ?
Many Thanks
Rob
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [June 11, 2020, 11:55pm UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/16 "2020-06-11T23:55:08Z")
Why do you want transactions that are only less than a year old to be updated?
The Vlookup and Arrayformula takes care of all transactions that are recorded into the App: Sales sheet if that’s what you mean.
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [June 12, 2020, 7:45am UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/17 "2020-06-12T07:45:07Z")
ThinhDinh,
The sale is to allow access to a module for a year. I therefore want to exclude any sales over a year old so as to remove access.
Regards
Rob
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [June 12, 2020, 8:20am UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/18 "2020-06-12T08:20:46Z")
Quick solution would be create an additional column in the App: Sales sheet that calculates the difference between the paid date and today.
Then for the VLOOKUP filter, add the filter that the calculated column is less than 365 days.
---
### Author:  [@Robert\_Turner](https://community.glideapps.com/u/Robert_Turner)
#### Post date: [June 12, 2020, 8:38am UTC](https://community.glideapps.com/t/help-with-app-script-data-manipulation-in-arrays/10669/19 "2020-06-12T08:38:21Z")
Great Thank you