Login with Google Sheets

MIT App Inventor Help
1

I ask for help to create a login simply consisting of USER and PASSWORD, therefore without giving the possibility to register. I will be the one to insert the users I want to authorize to use the application in the Google Sheets spreadsheet.
In practice, what I thought I would do is that I insert Google Sheets user and password in the spreadsheet, which I will then communicate to the user who will log in. At a later time, if I decide not to allow access to any user anymore, I just need to delete the gredentials in the google sheet.
I kindly ask if someone can help me to fulfill this need.

2
  1. Why (Excel) in the title ?
  2. Will the google sheet need to be private?
  3. Will the users be posting data to the google sheet or is this just about login to the app ?
3
  1. I put excel just to make it clear that it is a spreadsheet.
  2. The google sheet must be private.
  3. Only I have to access the google sheet, only I can enter username and password in this sheet. The application should refer to this sheet to check if the username and password entered by the user in the app login screen are correct. Each user will have their own username and password
4

You will need a google apps script web app in order for your users apps to connect to the login data on the spreadsheet.

I can prepare a simple example, tomorrow...

5

I really thank you very much.

6

I do not know much about Google Sheets, but can you take a look at Tim's website?

7

Thank you for the prompt reply, I have already seen Tim's article, it is very complex because there is also the recording and honestly I have not been able to interpret my case in the article.

8

This should get you going, (assumes no header row for the data on the sheet). All the work is done in the web app, no usernames or passwords are returned to the AI2 app:

SHEET
image

SCRIPT

///// check username and password and return output
function doGet(e) {
  var message = "";
  var ss = SpreadsheetApp.openById(e.parameter.sheetId);
  var sh = ss.getSheetByName(e.parameter.gridName);
  var rg = sh.getName() + "!" + sh.getDataRange().getA1Notation();
  var sql = e.parameter.query;
  var qry = '=query(' + rg + ',\"' + sql + '\",0)';
 
  var ts = ss.insertSheet();
  var setQuery = ts.getRange(1,1).setFormula(qry);
  var getResult = ts.getDataRange().getValues();
  ss.deleteSheet(ts);
  
  if (getResult[0][0] == "#N/A") {
  message = 'username not present';
  } else {
  if (getResult[0][1] != e.parameter.pwd) {
  message = 'incorrect password';
  } else {
  message = 'signed in';
  }
  }
  return ContentService.createTextOutput(message);
}

BLOCKS

blocks (1)
blocks (1)668×790 86.6 KB

SCREENS
image

image

image

image

image

GUIDES

AIA
GSLogin.aia (3.0 KB)

4 Likes
9

I'm sorry, I've been trying without results for days, it's not clear to me what I have to indicate where it says "obfuscated", I assume that the url generated by the google spreadsheet must be entered in the first line, but in the other I cannot understand what I have to enter.
Excuse me again and thank you

10

The variable is called scriptUrl. This is where you enter the...script url, you see this when you have published the web app. It looks like this:

https://script.google.com/macros/s/AKfycbw9....He2iPE6-dI/exec

The second obfuscated text is the sheetID. You get this from the url address bar when you have the spreadsheet open. it looks like this:

https://docs.google.com/spreadsheets/d/1BtafR_dY6zwv....OLYL6nmq0Jwo/edit#gid=0

You only need this part:

1BtafR_dY6zwv....OLYL6nmq0Jwo