And you would want your google sheet to remain private on the web ?
1 Like
Yes I would.
OK, I won't get this finished tonight, but sometime tomorrow.
Meanwhile, you will need to read up on this:
https://ai2.metricrat.co.uk/guides/howto-create-a-google-apps-script-web-app-bound-to-a-spreadsheet
(I will explain...)
1 Like
Thank you Tim, i read the link you gave me, I tried the blocks and they work ( I had no doubt).
I ask again if you think that using sheets may be sure.
If possible I wait for the example you told me, I accept any suggestion you can give me.
Thanks a lot.
Sergio
In my opinion, maybe you should make a tutorial to create a login page using Google Sheet.
Regards,
Salman Dev
Well done for following the howto!
Here is a simple solution, that requires a user of the app to register their email and password, then allows them to login, and then allows them to submit a simple report.
In order to keep the google sheet private, we use a google apps script web app that runs as "you", but you allow "anyone, even anonymous" to run it. The web app provides HTTP GET facilities for the registration, login, and report submission. Here is the code i used in the web app:
function doGet(e) {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("Logins");
var sh = ss.getSheetByName("Reports");
if ( e.parameter.func == "register" ) {
var email = e.parameter.email ;
var passwd = e.parameter.passwd ;
sheet.appendRow([email,passwd]);
return ContentService.createTextOutput("New User Added");
}
else if ( e.parameter.func == "testLogin" ) {
var msg = "Incorrect Login";
var email = e.parameter.email;
var passwd = e.parameter.passwd;
var loginData = sheet.getDataRange().getValues();
for ( var i = 0; i < loginData.length; ++i ) {
if ( email == loginData[i][0] && passwd == loginData[i][1] ) {
msg = "Logged In";
}
}
return ContentService.createTextOutput(msg);
}
else if (e.parameter.func == "report") {
var email = e.parameter.email ;
var date = e.parameter.date ;
var data = e.parameter.data ;
sh.appendRow([email,date,data]);
return ContentService.createTextOutput("Report submitted");
}
}
I have kept the login data and the report data on the same spreadsheet for simplicity, but you can use different google sheets if you wish. The login data, if successful, allows the user to progress in the app to report submission, it does not provide the app user any access to the google sheet. You will no doubt want to add additional fields for your reporting, just amend the url you submit in the app, and amend the appendRow section in the script accordingly.
The example app uses three vertical arrangements as virtual screens, one for login, one for registration, and one for report submission. Here are the blocks used:
This is what the two grids on the spreadsheet look like:
Logins
Reports
I attach an aia project for your use:
GSLoginReports.aia (7.9 KB)
As a reminder, every time you make any changes to your google apps script, you need to publish a new version.
A simpler method without reports, just register and login:
5 Likes
Would Firebase for Register and Login, and submit data to Google Sheets be a better approach ??
- The OP asked how to do it with Google Sheets
- Firebase over complicates things in this instance
1 Like
I really thank you very much TIM. I will try to apply your solution, Hoping I will be able to solve my need.
Thanks again.
There must be a way to Register and Login using Google Sheets as a repository WITHOUT Google App Scripts !?
Yes, you could use a google form submit for registration and grab the data as a csv to test login credentials, but the google sheet would have to be set with "anyone who has the link" permissions as a minimum, which means that the sheet is not private and available to anyone......
That said, the chances of someone finding/working out your sheetID (brute force) are @ 4 billion to one , all depends if you want to take the risk
@TIMAI2
Just experimenting Options !
Worth a try !
Doing it. Done the registration part. Trying to implement validation to check if userName already exists ! Will work it out ...
You could always use a script onFormSubmit to encode/encrypt the login data, casual sheet viewers would not be able to access the script to easily decode...then have the decode/decrypt back in the app.
Thats exactly what I iintend doing, but not on the google sheet. I intend using the encrypt/decrypt in the app, so that encrypted text is stored on the google sheet.
This is what I have done.
It is working,
But I am not satisfied !
Register_N_LogIn.aia (7.9 KB)
Can u suggest ??
Rectified the error !!!!
Good to go now !
Earlier I was comparing LogIn id with DB_Value and pwd with GetValue from the sheet.
SO !, een if userid was Not on the sheet, and any other user had SAME pwd, it was loggin in.
I had to compare the user id to GetValue from Sheet and pwd also from Get Value from sheet, and then permit LogIn. Dont that. Working fine now !
Now doing the encrypt and decrypt part
Encrypted Password
I would suggest you encrypt the username as well, and change the headings to something obscure (not User/Password)
Roger Sir WILCO
How are you doing the encryption?