Oopsie! I just didn't notice that I have entered 'mm' insteast of 'MM' in this line..
var formattedDate = Utilities.formatDate(fDate, "GMT", "dd-MM-yyyy")
Thanks to you TIM!
You should reverse it so you can sort: yyyy-MM-dd
Also I see that the dates have been considered to the previous day. For instance, the date 20-1-2024 has been entered as 19-1-2024.
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", "dd-MM-yyyy")
Logger.log(formattedDate);
jsonData.push([formattedDate, systolic, diastolic]);
Your script may be running in a different time zone
Yah!! It's resolved now.. but the data is not plotting in the app. Why so?
You need to show your workings.
The sheet, the script and your blocks need to be working together.
Show all of this, which may reveal why you do not see the data in your app.
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);
}
Try setting the formattedDate to a string
jsonData.push([formattedDate.toString(), systolic, diastolic]);
No, It's not working.
You need to have a header row of strings, your data (arrayTable) should have this format:
in your case:
[
["Date", "Systolic","Diastolic"],
["2023-01-19",128,114],
["2023-01-20",140,123],
....
]
How do I do that?
See here for an example:
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:
On clicking the 'Login' button, the user will be logged in, and the existing sheet has to be accessed.
I am not getting the response in my apps script. Can you help, please?
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?
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?
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"
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";
}
}
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 ?
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..
Do you have a return content service at the end of your script, to return msg?