Hello,
I've successfully implemented a system to gather user data and post it to a Google Sheet. Now, I'm looking to enhance the functionality by enabling access to specific user sheets based on the username entered on the login page. I would appreciate any assistance or guidance on how to achieve this.
Thank you.
If you want than the user A access only to the sheet "A", then you can rename the sheets with the user names and use it to access so, user A will access to sheet A, user B to sheet B....If you want a complete login procedure may be this can help you:
Register/Login with Google Sheets - Tutorials and Guides - MIT App Inventor Community
1 Like
Do you want the user to work directly on the google sheet, or simply access their data? If the latter, there is probably no need for individual sheets, just store all their data in a row and retrieve it as required, giving access to that data only to the specified user.
Hi Tim,
The users simply ought to access their data. But also they'd have access to view their latest data as well as their data plotted for past 7 or 30 days (if they opt to). So, I thought it's be easy to keep track if we have separate sheets. Any thoughts?
Without seeing what it is you are doing, with examples of your data, per user, it is difficult to comment further....
Show us sample data
This is my login page
The userID that I obtain here, I want it to reflect in sheets. So, I implemented this function in the script, along with other functions called in the doGet(e) and doPost(e) :
function GetGoogleSheet(userID){
var SheetID = userID;
ss = SpreadsheetApp.getActiveSpreadsheet();
}
function doGet(e){
//var userID = e.parameter.userID;
GetGoogleSheet(userID);
addUser(e,sheet);
ReadSheetData(e);
if (e.parameter.history == 30) {
return lessthan30Rows();
}
else if (e.parameter.history == 7) {
return convertLast7RowsToJson();
}
else if (e.parameter.history == "DisplayData") {
return ReadSheetData();
}
else {
return lessthan7Rows();
}
}
function doPost(e) {
var userID = e.parameter.userID;
GetGoogleSheet(userID);
addUser(e,sheet);
return ContentService.createTextOutput("Received sheet name: " + userID);
}
But, I'm not sure where to obtain the userID from the app. Should I place it in doGet(e) or doPost(e)?
Also I have named the sheetname same as that of the userID that I'm obtaining in the app.
Here is the sheet that I use store and retrieve data
What you have shown so far raises many questions....
- How does a user get registered ?
- Could the new user's sheet be automatically created at registration ?
- Are passwords hashed, given this contains potentially confidential patient information ?
- Who enters the data, the user ?
- How will you display the data on return of a query by the user ?
You should only need to use doPost(e) in your script, this will help to ensure that data is only accessed through the app.
UserId should be available to the user at login
We provide them with the username & password.
Yes, it should. (Yet to be implemented once this issue of accessing existing user's data sheet is resolved)
This app developed is gonna be a private app. Also, the physician and the patient only know of the details. Hence, the passwords are not hashed.
The physician enters the data.
The app features a "Display Data" btn, then the last row in the user's data from the sheet is retrieved.
Sounds like you need a doctor (admin) app and a patient app?
No, no two separate apps. For now, let's say, it's from the physician pov.
You could do it all in the same app, have an admin area and patient area
I appreciate the suggestion to integrate an admin area and patient area into the same app. However, I believe that focusing on "accessing the respective user's sheet on logging in" is crucial for achieving our immediate goals.
The problem I face is: when I enter the userID in the textbox of the login page, and use the Web component to Post the 'userID' to the apps script, it is not reflected in the above apps script functions.
Agreed, but it may make sense for the user's sheet to be created by script as opposed to manually (avoid errors extra work) and keep everything in one place.
I needed to understand your workflow before offer solutions to your script, which is obviously not working for you....
Also, it may make life easier if you enter your dates as strings and in an alpha-numeric style e.g.
2024-01-15. These can then be sorted/handled by AI2 whilst a date number may prove more complicated.
The code you are probably after:
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetbynamename
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheets
Thank you Tim. But I have a doubt.
When I send the 'e.parameter' content from two different screens and different textboxes but passing it inside the same function, from the app to the apps script, does it affect its reflection in the apps script?
function doPost(e) {
var userID = e.parameter.userID;
GetGoogleSheet(userID);
addUser(e,sheet);
return ContentService.createTextOutput("Received sheet name: " + userID);
}
function addUser(e,sheet) {
//Patient_ID = e.parameter.Patient_ID;
Date = e.parameter.Date;
Systolic = e.parameter.Systolic;
Diastolic = e.parameter.Diastolic;
sheet.appendRow([ , Date, Systolic, Diastolic]);
Script like this ?
function doPost(e) {
var ss = SpreadsheetApp.GetActive();
var sh = ss.getSheetByName(e.parameter.userID);
sh.appendRow([e.parameter.userID, e.parameter.Date, e.parameter.Systolic, e.parameter.Diastolic, e.parameter.Indicator]);
return ContentService.createTextOutput("New record saved to " + userID);
}
Also in your blocks set your date: Year-Month(number)-Day(number) e.g. 2023-12-25
You should be able to send the data from any screen if you have the correct variables available
It's still not working. Can you look into it for me? I'm attaching my script, sheet, and app in .aia file
BP_App.aia (1.0 MB)
And I've also changed my date format too as you suggested 
It may be easier to clear out and start again with your script and blocks.
Do one thing at a time, get that working, then add the next thing.
You could start with this for data entry:
As I have changed the date format, I had to change my script and I have obtained the jsonString as like this:
[["Date","Systolic","Diastolic"],["20-11-2023",124,84],["27-11-2023",120,80],["28-11-2023",126,86],["28-11-2023",125,85],["18-01-2024",120,80],["19-01-2024",120,85],["19-01-2024",123,83]]
It's not plotting in the app. Why is that?
Some interesting dates you have there ?
You changed your script ?