Maximum Date/Timestamp

[Anup_Ludra]

I have reverted back to the VLOOKUP queries

[TIMAI2]

I am not seeing the behaviour you show in the second image, although I have tidied up the formula a bit at this end.

1. Move the formula to another sheet, google forms responses do all sorts of strange things.
2. When removing data from responses, remove only the data, not the rows. You are correct in clearing the data regularly, this will keep the formula response time down.

Use this: (paste it to your responses sheet cell, then you can cut and paste to another sheet and the range links will follow.)

=sort(filter($A:$D,match($A:$A,query($A:$D,"Select max(A) GROUP BY B ORDER BY max(A) desc LABEL max(A) ''" ),0),match($B:$B,query(query($A:$D,"Select B, max(A) GROUP BY B ORDER BY max(A) desc LABEL max(A) ''" ),"Select Col1"),0)),1,FALSE)

[Anup_Ludra]

1. I may also be possible, that I have been CHANGING the NAME after it is uploaded from the app. This WORKS sometimes, and sometimes, it shows the SAME NAME MORE THAN ONCE.

2. I will change the NAME in the app and upload data as multiple users rather than CHANGE the NAME onthe Google Sheet.

3. Tying. Will revert.

[Anup_Ludra]

1. Tried it.
2. Did not CHANGE Names on Google Sheets, but changed Name on the Android app to depict MULTIPLE USERS. Changes Lat Long Values on Google Sheets FOR EFFECT on the map.
3. Seems to be GOOD TO GO !
4. Will still test exhaustively and revert.

[Anup_Ludra]

Does Google Sheets query have a DELETE ??

1. I am getting a COUNT of the ROWS of data added.
2. If count($A2:A)=100 THEN delete($A2:D) ????

[TIMAI2]

You will need a google apps script to delete the rows.

Do you need to keep the old data for reference purposes? If so you could copy the data to another sheet?

[Anup_Ludra]

Old data is Not required. Just the Last reported position is required

Am trying ....
If A20 IS NOT NULL, DELETE A2:D15

[TIMAI2]

Here is the script:

function onFormSubmit() {
  ss = SpreadsheetApp.getActive();
  sh = ss.getSheetByName('Form responses 1');
  rg = sh.getDataRange().getValues();
  if (rg.length > 10) {
  sh.deleteRows(2, 3); //deletes from second row and deletes three rows
  }
}

Make sure there is nothing else on Form responses 1 other than the data

Replace rg.length > 10 with the number of rows you want to test for, e.g. rg.length > 100
Replace the number of rows you want to delete e.g. sh.deleteRows(2,50);

In the Script Editor:

Click on Edit then Current project's triggers
Click the blue button bottom right to add a trigger
Change "Select event type" to "onFormSubmit" and Save

This will run the script every time a new entry is made.

If you prefer you can set the script to run every hour or every day....

[Anup_Ludra]

Lemme give this scripting a shot.

Havent worked on google app scripts yet

[Anup_Ludra]

@TIMAI2

function onFormSubmit() { ss = SpreadsheetApp.getActive(); sh = ss.getSheetByName('Form responses 1'); rg = sh.getDataRange().getValues(); if (rg.length > 10) { sh.deleteRows(2, 3); //deletes from second row and deletes three rows } }

I been trying gogle app scripts earlier. But I always get this error

[TIMAI2]

I have never seen this myself, but have seen that other people have got it.

Exactly when does it happen in the process ?
Have you authenticated the script to run on your account?

[Anup_Ludra]

When u Click on Publish and select on Deploy as Web App
Then u select Anyone, Even Anonymous and Click on Update
U get a prompt to Review Permission
When u clik to review, u are asked to select email account
When I select my email account

That is when this ERROR comes up

Thats why I been AVOIDING Google App Scripts

[TIMAI2]

1. it is not a web app, just a script that runs on the spreadsheet. If you have a web app running there already you will need to create another project.

See here:

"Sign in with Google temporarily disabled for this app" error when trying to authorize a script

sometimes the only way to resolve is to start again from the beginning with a new spreadsheet and script project.....

[Anup_Ludra]

@TIMAI2

function onFormSubmit() { ss = SpreadsheetApp.getActive(); sh = ss.getSheetByName('Form responses 1'); rg = sh.getDataRange().getValues(); if (rg.length > 10) { sh.deleteRows(2, 3); //deletes from second row and deletes three rows } }

I deleted the project, Which I was trying to publish as a web app.
Created a new project
Put ur code in it and saved it

[Anup_Ludra]

Is this OK ??

How is it supposed to work??

[Anup_Ludra]

@TIMAI2

I created a new project and shared it as Editor with another email id.
Logged on with the other id, and created a trigger for this Project(DeleteRows)
It has been created (Time Interval Based. Testing it Every Minute).
But it is not working.
I have checked the Trigger Executions and snapshot is attached

OK !!!
I Deleted this project too and created another projected, and started from scratch.
Though I did not create a new sheet.
This time too, I had to share the sheet with the other id to get it functional.
The trigger is FUNCTIONAL NOW as OnFormSubmit. Earlier it was not giving this option.

Instead of (2,3) I have made it (3,5)
and left it as >10
Awesome learning !!
Tx.

[Anup_Ludra]

@TIMAI2
This Google app script u sent has been a life saver. I am new to this stuff, but this approach can help automate a hoard of stuff I been trying for quite some time.

Can something similar, automate the process of UPDATE an existing row ??

1. If a record by a user has been submitted, and another record by SAME user is uploaded, instead of getting a UNIQUE record by max(Timestamp), aim is to UPDATE the record, by ADDING up data in the other columns.

2. If a record by a user has been submitted, and another record by SAME user is uploaded, instead of getting a UNIQUE record by max(Timestamp), aim is to UPDATE the record, by REPLACING data in the other columns.

(If I am able to achieve a UPDATE record by REPLACEING previous data, then the requirement of getting the UNIQUE DISTINCT record is done away with and also the requirement of DELETE rows subsequently is NOT required. So when data is uploaded, it checks for Name, if Name exists, then REPLACE, else append row)

Trigger in both cases would by OnFormSubmit

[TIMAI2]

You have been quiet, so I have surmised it is all working OK

Now to your questions....

@Anup_Ludra

Can something similar, automate the process of UPDATE an existing row ??

1. If a record by a user has been submitted, and another record by SAME user is uploaded, instead of getting a UNIQUE record by max(Timestamp), aim is to UPDATE the record, by ADDING up data in the other columns.
2. If a record by a user has been submitted, and another record by SAME user is uploaded, instead of getting a UNIQUE record by max(Timestamp), aim is to UPDATE the record, by REPLACING data in the other columns.

Modifying records submitted via google forms is OK, as long as you NEVER go back into the form and try to update responses there. That said, finding the correct record to UPDATE might be fun....

Could you please show (on a spreadsheet) what you want to happen e.g. existing record, new record, final result for both 1 & 2?

[Anup_Ludra]

For me this coding and application development is a passion and a hobby. Professionally, I am a Colonel in the Army. So was busy with other work.

Will create a spreadsheet and upload sample data, instead of meddling with the spreadsheets in use.

[Anup_Ludra]

Sample data

Situation 1

User has uploaded data, and wants to UPDATE by REPLACE, maybe due to wrong data having been uploaded earlier. So when New record is submitted, if Name exists, then PREVIOUS record is DELETED, else new row is appended

Situation 2

User has uploaded data, and wants to UPDATE by ADDING, maybe due to having missed out some data earlier. So when New record is submitted, if Name exists, then PREVIOUS record is UPDATED by SUM of ranhge of other columns, else new row is appended