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]
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.
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
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.
sorry my mistake
again many thanks for your help
1 Like
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!
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
Hi, I have just removed the arrayformula from the email and the name column. You’re good to go now.
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
If you’re ok with it you can temporarily share your main sheet to ariesarsenal@gmail.com. I’ll have a look.
ThinhDinh,
Thankyou will do the only two sheets relevant for this are
AuthUsers
App: Sales
shared now
Many Thanks
1 Like
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).
ThinhDinh,
Thankyou really appreciate all your help and patience
Thanks again
Rob
1 Like
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
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.
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
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.