App script to delete a row in Google Sheets after a defined period of time

[Mathias Büschlen]

Hi Google Apps Script Community

I am looking for a script or someone who can build me a script for the following function:

In a Google Sheet I want to delete a whole row after it was entered 5 days ago. I have the date of the entry in one cell per row. The Google Sheet row is filled from a Typeform.com form.

Thanks for any help and best regards
Mathias

[Keith Andersen]

Can you share your sheet?

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1979a8e7-a3f3-412e-8a78-91853035261an%40googlegroups.com.

[Mathias Büschlen]

Hi Keith - sorry for the late reply.
Very happy to send the Google Sheet (the row 2 should be deleted automatically after 5 days.) Date / time will be found in the column "J" (Submitted At).
Thank you and best regards
Mathias

[Keith Andersen]

Mathias,

As I prepare this for you- are you familiar with App Script in general? Giving initial permission for the function to run? Working with the script editor?

Keith

Passions: God, Family, Friends, Scripture, Data Management, Google Sheets + App Script, MS Access, Programing, sharing and much more.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/06698276-82ef-4421-9730-daa2feeda879n%40googlegroups.com.

[Keith Andersen]

In addition to my previous email questions...

will there be a time that you have multiple rows whose date is 5 days or older to delete?  I assume you're getting multiple responses from the form and need to weed out those that are 5 days or older?

Would you need an option to take the older date rows and instead of deleting them, move them to another sheet?

Keith 

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

[Mathias Büschlen]

Hi Keith

 

Thanks for the questions.

I am not yet familiar with app scripts (hopefully I will get this right) I know HTML / CSS and a little Javascript.

I probably won't have many entries at the same time (it's not tragic if this stays up for 1 day longer).

Could I still do this manually (delete whole lines)?

No, these lines can definitely be deleted.

For your information: these are simple "advertisements" which hosts can publish on the club page. "I currently have this period free" or "I have a request from guests for".

 

Thank you very much already!

Greetings Mathias

 

 

 

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/60xreMQeeqI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BGoFOJ1LY75g-PpKPnnhSU_bLFUka-sGtKCe0Mx9cOxhQ%40mail.gmail.com.

[Keith Andersen]

Yes, it is perfectly okay to select the row and delete it on the sheet one at a time. 

I have formulated the script to accomplish this via clicking a menu item. This will search all rows in the spreadsheet and delete those that are 5 days beyond the date in column J. 

With all scripts, you have to copy and paste them into the apps Script Editor and then run one of the functions to give initial permissions for the script to run. That is why I asked if you have that basic knowledge. 

I would be happy to install the script for you on your sheet. You simply need to share it with me privately at:

contact...@gmail.com

If it has sensitive data you would rather not share, I completely understand and that would leave you with simply deleting it manually.

Let me know.

Keith 

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/AS4P250MB07288043936CBF022ED35966F05E2%40AS4P250MB0728.EURP250.PROD.OUTLOOK.COM.

[Keith Andersen]

I have a Dr appointment I'm getting ready to go to. I will be on my computer tonight. I will email back when done. 

Cheers

Keith 

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

[Mathias Büschlen]

That's terrific! I've just invited them to the files. These files are filled by Typeform.

These typeforms are not yet released - but will be published on the website.

Typeform for Sheet Nr. 2: https://vrk5rt7m276.typeform.com/to/SWlWyS7w

Perfect! Many Thanks!

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BFvSe9oCy_0mmYfLFhZ_Vhoj_LZYaf7EvN5jR171E54jA%40mail.gmail.com.

[Keith Andersen]

I have tried through Google translate to put comments and confirmation messages in German. Hope I got that right.

Keith 

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/AS4P250MB0728640B35ADF640D8CAC21AF05E2%40AS4P250MB0728.EURP250.PROD.OUTLOOK.COM.

[Keith Andersen]

Mathias,

I put the script in the sheet and tested it.

I created a video to explain it and demonstrate it. See below link.

https://youtu.be/oITxElt8oS4

I'm not sure what I need with the Anfragen FeWo PVA sheet?

The code references specific rows/columns and tabs. It is case sensitive. If you change the name of the tab or add columns before J - the code will break.

If you have any questions feel free to reach out.

Keith.

Passions: God, Family, Friends, Scripture, Data Management, Google Sheets + App Script, MS Access, Programing, sharing and much more.

[Laurie Nason]

Just to add to Keith's solution - I try to do my date calculation on the google sheet as a formula in a column - as javascript and dates are a p.i.t.a.! So I would create a column that uses datedif between today and the date you want to check and have it return a true if it's more than the 5 days you mentioned. Then your script only needs to check for true to know whether to delete the row or not.

Just my 2c worth.

Laurie

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BF6Rm_ReAsmWm%3DhUG65hzAuQBXXd%2BireWZDKCxkOM%3DhLQ%40mail.gmail.com.

[Mathias Büschlen]

Hello Keith

Thank you - the video is still set to "Private". I can't watch it that way.

 

The sheet "Enquiries FeWo PVA" needs the same function (delete row after 5 days").

Is the script automatically executed daily or is it currently the case that I have to execute / start this manually?

Thanks for your work so far! I will be very happy to show my appreciation!

Greetings Mathias

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BF6Rm_ReAsmWm%3DhUG65hzAuQBXXd%2BireWZDKCxkOM%3DhLQ%40mail.gmail.com.

[Keith Andersen]

I shared the video private - to your Google email. Sign in to watch it.

I will put it in the other sheet this afternoon.

Cheers

Keith 

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/AS4P250MB0728BCE4E93C71A420175DEFF05C2%40AS4P250MB0728.EURP250.PROD.OUTLOOK.COM.

[Mathias Büschlen]

I am logged in - but the message appears: "Video not available - This video is private" - no stress, there is no hurry :-)

 

Cheers Mathias

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BG3-cCkoABbKXbyje8Nsh9tJ7VdSexeUYoCp58d%2BcvG%2BA%40mail.gmail.com.

[Keith Andersen]

https://youtu.be/oITxElt8oS4?si=hn02TtY2QPFjwc8L

Try now.

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/AS4P250MB0728D420D6F34AAE316EBE1AF05C2%40AS4P250MB0728.EURP250.PROD.OUTLOOK.COM.

[Mathias Büschlen]

unfortunately I still get the same message

 

Cheers Mathias

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BGr9AX_8HaR1ftDKBYvQJniAGpfVfyXC78Bwy5GwgzrTw%40mail.gmail.com.

[Keith Andersen]

I changed it to public so let me know if you can see it now.

Keith 

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/AS4P250MB07287A56E3703A4763DECCD8F05C2%40AS4P250MB0728.EURP250.PROD.OUTLOOK.COM.

[Mathias Büschlen]

Now it has worked!

 

Perfect - thank you very much!

Is it also possible to set this script to start automatically once a day?

 

I am very grateful for your help!

Greetings Mathias

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BFmz1wWe7eJLsFANcSA0E8PzH1NFuMCO2jY2goRBOpr-g%40mail.gmail.com.

[Keith Andersen]

You will have to set it on your end. I will make a video showing exactly how to do it. 

👍

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/AS4P250MB07288EEE3EDC51021F109FAFF05C2%40AS4P250MB0728.EURP250.PROD.OUTLOOK.COM.

[Keith Andersen]

Here ya go.

https://youtu.be/wjIqTPYzqvQ?si=bUZWrWEAKynEdJXd

Cheers

Keith 

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

[Mathias Büschlen]

Hey Keith

Thank you so much! That is sensational!

 

I will install the trigger before the new website goes online.

 

First - how can I show my appreciation for this service?

Do you have a rate?

 

Thank you and best regards

Mathias

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BGkey2B-VWFhOcRrcce5%3DSWufm6Nbe%3DtmxMVctCUr9SXg%40mail.gmail.com.

[Keith Andersen]

Mathias,

Thanks for the offer, but it's all good. Glad to help out. Feel free to reach out should you need help with anything.

Cheers

Keith 

Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/AS4P250MB07286185F4EAB2A3D10D8A0AF0272%40AS4P250MB0728.EURP250.PROD.OUTLOOK.COM.