Is there a way to separate date in google sheet/csv file using AI2?

[Xiaomy Redmii]

I am using google sheet/csv 

I already get and post data in the google sheet using AI2 and some sort of coding in the google script

but I have the issue on separating the data into weekly & monthly

need help guys to separate them

or it will be separated in the google sheet itself?

thank you so much

[TimAI2]

Suggest using google sheets and apps script to resolve this.

An example set of data and a full description of what you want to do, relevant blocks if applicable, the "some sort of coding in google script" used, will help us to advise you

(Seems we have to say this every time you ask for help! Please do as above next time. The more information you provide to start off with, the easier it is to diagnose the problem or to provide advice)

[Xiaomy Redmii]

function doGet(e) {

  

  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1Rkv-0ZtwkeYVZbeo8zXMj3frJaFNwPuMy9tJYbrMkvA/edit#gid=0");

var sheet = ss.getSheetByName("Sheet1");

  

  Addfiles(e,sheet);

}

function doPost(e) {

  

  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1Rkv-0ZtwkeYVZbeo8zXMj3frJaFNwPuMy9tJYbrMkvA/edit#gid=0");

var sheet = ss.getSheetByName("Sheet1");

  

  Addfiles(e,sheet);

}

function Addfiles(e,sheet) {

  var status=e.parameter.status;

  var distance=e.parameter.distance;

  var temperature=e.parameter.temperature;

  var humidity=e.parameter.humidity;

  var location=e.parameter.location;

  var date=e.parameter.date;

  var collector=e.parameter.collector;

  

  sheet.appendRow([status,distance,temperature,humidity,location,date,collector]);

  

}

[Xiaomy Redmii]

[TimAI2]

This is a google sheets question as opposed to an AI2 question, but humour me.

Sort the data weekly and monthly?

The data is already sorted in date order (last entry last), however if the data is coming in from different devices then this may not always be the case....

a simple addition to the google apps script to sort the sheet based upon the date/timestamp can be added

Do you want to have the option to filter the data to just a set/specific week or month or want each week or month sorted, but all in one list /sheet?

You can set up additional tabs in the spreadsheet for sorting weeks / months as required, along with another tab for the filter/query input, which can be sent by the app to the sheet

See this example, which returns a pdf of filtered results to the app, but it can be just csv data returned instead:

AI2 and Google Drive/Docs/Sheets

HOWTO: Use google visualisation query and download filtered output as pdf from Google Sheet

[TimAI2]

To sort the main sheet (Sheet1) by date add the following code below your sheet.appendRow statement:

sheet.sort(6, true);  //will sort column six ascending (true - use false for descending)

Now it may be that the "date" is being posted as text, and not being recognised as a date

which may mean having to manipulate that entry into a date/time number.....

[Xiaomy Redmii]

okay sir thank you I will read about it

what is the code sir? 
I just add it ?

or need first study the link you send?

[TimAI2]

Was i not clear about where the code goes?

In your google apps script:

to sort the main sheet (Sheet1) by date add the following code below your sheet.appendRow statement:

sheet.sort(6, true);  //will sort column six ascending (true - use false for descending)


so it looks like this:

....

var collector=e.parameter.collector;
 
  sheet.appendRow([status,distance,temperature,humidity,location,date,collector]);

  sheet.sort(6, true);
....

[Xiaomy Redmii]

ah okay
so that's the solution?

[Xiaomy Redmii]

function doGet(e) {

  

  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1Rkv-0ZtwkeYVZbeo8zXMj3frJaFNwPuMy9tJYbrMkvA/edit#gid=0");

var sheet = ss.getSheetByName("Sheet1");

  

  Addfiles(e,sheet);

}

function doPost(e) {

  

  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1Rkv-0ZtwkeYVZbeo8zXMj3frJaFNwPuMy9tJYbrMkvA/edit#gid=0");

var sheet = ss.getSheetByName("Sheet1");

  

  Addfiles(e,sheet);

}

function Addfiles(e,sheet) {

  var status=e.parameter.status;

  var distance=e.parameter.distance;

  var temperature=e.parameter.temperature;

  var humidity=e.parameter.humidity;

  var location=e.parameter.location;

  var date=e.parameter.date;

[Xiaomy Redmii]

sir after I applied this

it will automatically separate daily to weekly and monthly??

should I add another 2 listview in my project??

[TimAI2]

No, all the extra line will do is sort the data by the date field, assuming the data therein is recognised as a date.

Because you have not told us what you mean by sorting by week or by month I have not gone there yet!

[Xiaomy Redmii]

weekly

when all saved data from first data  to all given accumulated data are classify as 1 week (7 days range)

monthly 

same as weekly

accumulated for month based on the calendar we're using

can be 28 days (feb) 30 days or 31

[Xiaomy Redmii]

the aim here is to accumulate data by means of separating/classifying them into 3 categories

daily

weekly

monthly


but the main data here are coming from daily

to have weekly you need to have 7 days range of data

and in monthly 28 / 30 / 31 

range of data

[TimAI2]

Would make more sense to develop a query on the data selecting a date range (based on daily or weekly or monthly)

Have a look at this to see how to query a google sheet using google visualization. Your spreadsheet has to shared "anyone with the link" for this to work

(you can ignore the filtering and pdf parts because you will want the complete data set)

You may find, to make things a bit easier, that you want to create new fields for your data for Month and Date only....

HOWTO: Use google query and download filtered output as pdf from Google Sheet

[Xiaomy Redmii]

the example sir it's not about dates

I have a bit knowledge on each process you did already on the post sir
but I don't know how to filter it by daily and weekly and monthly

[Xiaomy Redmii]

good morning sir

I know those sir but how to used this in separating / filtering weekly/monthly?

[TimAI2]

Do you want:

today / this week / this month ?

or

any day / any week / any month  ?

returned to your app ?

You only provided one row of data in your example above (as an image), if you have more data, as a csv, then that gives us something to work on.

[Xiaomy Redmii]

sir good evening

[Xiaomy Redmii]

the app will provide the data to csv 

[TimAI2]

Your Date and Time will need to be formatted in a way that google sheets recognises them as date and time, preferably as separate items:

Date: 2019-03-26    << year hyphen month hyphen daydate   << yyyy-MM-dd

Time 19:41:36    << hours colon minutes colon seconds   << 24 hour clock HH:mm:ss

example:

This also assists with sorting if the date/time is converted to a string, because they are both naturally listed e.g. 2019-03-26 -> 2019-03-27 -> 2019-03-28

[Xiaomy Redmii]

okay sir I will edit it

[Xiaomy Redmii]

sir how the separation on csv then?

[TimAI2]

There is a real world out there too that I have to engage with - work, chores, family.... :)

But i am thinking about your issue and will get to work on it as soon as i am able.

[TimAI2]

Here are the three queries needed (using strings for dates and months), and the months one allows for multiple years too

and the spreadsheet is anyone can access / view

MONTH

https://spreadsheet.google.com/tq?tqx=out:csv&key=1cKxYfTQqjhga5qAcDsMJncRchdOfpFOEFtdYo9ZHFKc&gid=1121632423&tq=select * where B > date '2017-12-31' and B < date '2019-01-01' and month(B) contains 6

WEEK

https://spreadsheet.google.com/tq?tqx=out:csv&key=1cKxYfTQqjhga5qAcDsMJncRchdOfpFOEFtdYo9ZHFKc&gid=1121632423&tq=select * where B > date '2017-04-01' and B < date '2017-04-08'

DAY

https://spreadsheet.google.com/tq?tqx=out:csv&key=1cKxYfTQqjhga5qAcDsMJncRchdOfpFOEFtdYo9ZHFKc&gid=1121632423&tq=select * where B contains date '2018-02-04'

These need to be worked into AI2 format with the ability to select by year, month or date/s, so a bit more to do yet

You can test out the above in a browser using my sample data, but you will need to replace "csv" with "html", otherwise you will be offered a download

[Xiaomy Redmii]

ah yes sir 

thank you so much
 :)

I will try the blocks I created on my app
and change/apply last night blocks

I will use file block, button and listview on each

I am also thinking about the other information under those dates

because each date has :

time

collector

distance

humidity

temperature

status

=

awesome you separated them nicely and daily/weekly/monthly
yeah you are right that the blocks needed only for those to return the values

but I think on the trash screen(s) each specific field just need to send the data on the google sheet and the google script will manipulate it and or the way you used it as like as database 

[TimAI2]

Here is a working example showing returns for today,this week, this month, any day, any week, any month

aia attached

SCREEN

BLOCKS

[Xiaomy Redmii]

awesome!

I wanna try it now

sir the csv file I am using will be manipulated using the blocks?

and still I will use the same code I'd used with sheet sort and true

[TimAI2]

For use with your own data:

Ensure your date and time fields are in the correct format (yyyy-MM-dd / HH:mm:ss) on the google sheet

You will need to change the column letters to suit your data e.g. B and C, work carefully through all the blocks

You will need to change the FileID for the google sheet and the gid (sheet) number

and also decide on the data you want to display on return, how many listviews etc (Web1.gotText)

[Xiaomy Redmii]

in my current project I am using 3 csv / google sheets and 3 scripts

because I thought each need to have one

so based on the app
you use only one google sheet/csv
and each was separate / segregate as one in the app

so I need to add TRASH number on my csv file so that it can also determine which from the 3 trash bin it come from

[Xiaomy Redmii]

sir on the date and time 

those two must be separated? unlike to my current app

[Xiaomy Redmii]

sir do I need to used ID? 

[TimAI2]

Date and Time

Yes, if you want to reuse my blocks then date and time as separate fields (for the third time!)

ID

You will need to use your own google sheets file ID

As said above, what I provided was an example, but it answers your question regarding filtering data by day/week/month

and provides methods for a variety of solutions.

[Xiaomy Redmii]

okay sir

sir I think I don't need this sheet.sort(2, true);

[TimAI2]

The rest of previous post retracted, apologies

[Xiaomy Redmii]

good morning sir

ah thanks I am currently working on the POST before I can used your aia

I am struggling in sending the data maybe because of sheet sort & true

[Xiaomy Redmii]

sir how did you get the url on your blocks?

=

I got it 
I modify it

sir I have 9 labels
based on your aia it has 4 labels
so I will add 5 more

but it will fit in my screen? 

=

I got all the data

but the text are horribly shown in the screen when I followed the 4 first listview you used and add 5 more list view

is this possible?

the screen occupied nicely until status but after that when the screen has already 9 listviews the text are not fitted to it

I fixed it by putting horizontal scroll arrangement and  changing font size

sir thank you so much for so much effort you exert just to help
don't worry I always memorize and study each blocks and logics you gave me guys here 
you are all the best
right now I am in the designing phase of the app 
all the functions are completed since I am started asking and learning MIT app Inventor here and IoT 
this was all a great time
Thank you so much!!
I will still continue learning and asking ;p 

[Xiaomy Redmii]

sir 
why list pickers and date picker is not invisible I already uncheck their visibility

[Xiaomy Redmii]

sir when I created the same aia of yours why I don't have the 

accent colors

datepicker and listpicker still visible in designing the app and different from the format when  ("any" buttons)

 are click

=

I observed because you are using the screen 1
this has a more prevelige in formats

[Xiaomy Redmii]

sir why our date picker pop ups are different

mine doesn't show a calendar like yours

but only shows a simple lang with up and down

[SteveJG]

The date picker display depends on whether  you select    a  Classic Theme (the default) or  Device Default   Theme.   The display you see will be slightly different based on the Android version of your device.  Each version of the operating system shows a slightly different  date picker calendar image.  The colors can be manipulated by changing PrimaryColor or AccentColor (on the Screen1 Properties) from there default settings provided you do not select Classic Theme... experiment to get the effect you want.

[Xiaomy Redmii]

sir can you help me with the DUPLICATE data

I already tried this https://developers.google.com/apps-script/articles/removing_duplicates

but it doesn't work

and I also watched the FILTERING of csv data directly on the google sheet but that wasn't practical because the data coming from my app will continuous sending data to the csv

[Xiaomy Redmii]

sir need help
I still not remove the duplicates in the google sheet

it should work automatically remove the duplicate data in the google sheet and only show the unique data in the listview

[Xiaomy Redmii]

good morning sir 

@Tim @Steve

do you know how can I put the current data on the first ?

it always print the latest data below

thank you

xiao

[SteveJG]

Sorry Xiao, we don't understand what you mean by this "do you know how can I put the current data on the first ?  ".it always print the latest data below

Does it mean put the current data from one of your arduino's on screen1 or something else.     Where are you printing the 'latest data below' and what does that mean?    It would 

help if you provided a picture of what is happening and tell us what you would like to do.     Do you mean you want to SORT the data with the most recent data first?

You can do that with   by sorting your list   List Sorting On App Inventor  and choosing the  ascending or descending depending order using a bubble sort possibly.

[Xiaomy Redmii]

the issue is in the viewing of data in the app
coming from the google sheet

yes the current/updated data must be on the first row

the option was given by TimAI2 since I already format my google sheet to descending order

see the aia

at the blocks there is order by B C 

I changed it to order by B DESC C DESC

or I can just remove it

because I already change the format on google sheet DATE data

thank you

xiao