Post user data to respective google sheets in the spreadsheet

MIT App Inventor Help
27

image

image
image792×395 41.4 KB

function Check_GoogleSheet(){
  ss = SpreadsheetApp.getActive();
  sheet = ss.getSheetByName("MHS_122");
}


function doGet(e){
  Check_GoogleSheet();
  if (e.parameter.history == 30) {
    return lessthan30Rows();
  }
  else if (e.parameter.history == 7) {
    return convertLast7RowsToJson();
  }
  else {
    return lessthan7Rows();
  }
}


function convertLast7RowsToJson() {
   sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('MHS_122');
 
   var dataRange = sheet.getRange(sheet.getLastRow() - 6, 2, 7, 3) ; //start row, start column, no.of rows, no.of columns
  var data = dataRange.getValues() ;
  var length = data.length;
  Logger.log(length);
    var jsonData = [];
 
    for (var i = 0; i < data.length; i++) {
    var date = data[i][0];
    var systolic = data[i][1];
    var diastolic = data[i][2];
    var dateParts = date.split('-');
    Logger.log(dateParts);
    var fDate = new Date(dateParts[2], dateParts[1] - 1, dateParts[0]);
    Logger.log(fDate);
    var formattedDate = Utilities.formatDate(fDate, "GMT+5:30", "yyyy-MM-dd")
    Logger.log(formattedDate);
    jsonData.push([formattedDate, systolic, diastolic]);
  }
  Logger.log(jsonData);
  jsonData.unshift(["Date","Systolic","Diastolic"]);
  var jsonString = JSON.stringify(jsonData);
  Logger.log(jsonString);
  return ContentService.createTextOutput(jsonString);
}

sheet

28

Try setting the formattedDate to a string

jsonData.push([formattedDate.toString(), systolic, diastolic]);

29

No, It's not working.

30

You need to have a header row of strings, your data (arrayTable) should have this format:

image

in your case:

[
["Date", "Systolic","Diastolic"],
["2023-01-19",128,114],
["2023-01-20",140,123],
....
]
31

How do I do that?

32

See here for an example:

33

This is still not working for me. But, I wanna let it rest and come back to it later.

Now, I'm working on the login page. It has two buttons - 'Register' and 'Login'.
On clicking the 'Register' button, the user will be registered in the firebase. Since, the user is new, we create a new sheet within the spreadsheet for the user. The block is as follows:
image

On clicking the 'Login' button, the user will be logged in, and the existing sheet has to be accessed.
image

I am not getting the response in my apps script. Can you help, please?

34

Why have you over complicated by introducing firebase as the login method?

It also appears that you do not check that your registration has been successful before trying to create a new sheet in your spreadsheet?

35

The registration is verified in the firebase platform.. isn't it?
(Do I have to write a little procedure to verify it?)

Also, why would you say firebase login method to be over-complicated?

36

Not the way you are doing it.

Because using your google sheet for your registration and login was working OK, and it keeps it all in one "datastore"

37

Apologies for any confusion in our previous communications. To clarify, I've been utilizing Firebase for the registration and login functionalities, not Google Sheets. Also, Firebase is the scope of login interface I was required to meet with.

So please can you tell me where I am going wrong in this process of: opening a new sheet for the new user and accessing the existing sheet for the existing user..?

var ss = SpreadsheetApp.getActiveSpreadsheet();
function doPost(e) {
  var msg = " ";
  if (e.parameter.action == register){
    var user = e.parameter.user;
    ss.insertSheet(user);
    msg = "New User Added";
  }
  else if (e.parameter.action == login){
    var user = e.parameter.user;
    ss.getSheetByName(user);
    msg = "Sheet accessed";
  }
}
38

Looking at your blocks, you are not really logging in. Where are you testing the password you set, and where are you setting the L_username_txt ?

39

no, I'm not logging in.. In the block, I'm trying to access the username entered by the user. 'L-username_txt' means username textbox in the Login Vertical Arrangement..

40

Do you have a return content service at the end of your script, to return msg?

41

yah.. I have it :+1:

42

Here I found the error to be those e.parameter.action values - 'register' & 'login to be in quotes.. now the function is working properly by creating new sheets & accessing them..

But, I have a problem in setting the accessed sheet to be the active sheet in the script. As I have mentioned before, I have other functions like 'Adding User', 'Obtaining User Data and Plotting them' to be executed within the logged in sheet. Passing this ss.setActiveSheet(sheet) in the 'login action' block doesn't solve it either.

43

You shouldn't or don't want to do that . The username defines the sheet to use, set this as a parameter in your blocks and script to interact with the correct sheet.

44

I have obtained the username as variable 'user' in the doPost() function. I am not able to pass this local variable in the other functions.
Here I have attached my script. so pls can you look into it?
AppsScript.txt (1.7 KB)
I am doing the registering and loging functions in one screen and the adding user data in the next of the app inventor.. does it affect the calling of the function in the script?

45

Try this script

AppsScript.txt (1.7 KB)

I have converted your local variables to global variables

46

Thank you, Tim. With the solution for accessing the user's sheet in place, I've encountered another challenge: encountering the error
'TypeError: Date.toDateString is not a function' . My date is in the format of 'dd-mm-yyyy'.