How do I create an Auto-Clear Script in a Google Spreadsheet?

Question

I could use some help with writing a script in my Google spreadsheet. First off, I am no programmer and a novice at best on writing code or any script.

I want to use this for a lunch list. The back story: I created Google spreadsheets to act as a digital lunch sheet. Each teacher has their own spreadsheet for their homeroom and the totals for the class populate a master lunch sheet for the head cafeteria worker for ordering. The problem is that the old totals are still present from the day before. Ideally, on the start of a new day, the specified fields on the spreadsheet will auto clear. Click here to view an example of the spreadsheet I created.

In my research I found a thread on how to create a script to do this, but as a button to click that will then clear specified ranges Click here to see the original post. The script is as followed:

function clearRange() {
  //replace 'Sheet1' with your actual sheet name
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  sheet.getRange('C4:I12').clearContent();}

What I would like for is there to be a script so that everyday (say everyday at midnight) a specific field/ range is cleared out. I do not want columns or rows to delete because I do not want to lose student names or lunch selections.

Please, any help is greatly appreciated. Thanks in advance for your help, especially with a novice like me. I look forward to hearing from anyone!
- Jason

Answer 1

Follow these steps...

01. Go to https://script.google.com and then add a New Script.

ᴬᵈᵈ ⁿᵉʷ ˢᶜʳⁱᵖᵗ

02. Paste this function

function clearRange() {
      // replace 'Sheet1' with your actual sheet name
      // replace 'dhrhrejYOURSHETIDerhe5j54j5j' with your actual sheet ID

      var sheetActive = SpreadsheetApp.openById("dhrhrejYOURSHETIDerhe5j54j5j").getSheetByName("Sheet1");
      sheetActive.getRange('A:Y').clearContent();

    }

ˢᵖʳᵉᵃᵈˢʰᵉᵉᵗ ᴵᴰ ᴱˣᵃᵐᵖˡᵉ

03. Goto Run & select Run function and then select clearRange.
Once you have run the script, your spreadsheet should be cleared.  

Follow these steps if it works correctly...

04. Goto Edit, Select 'All your triggers'

05. In 'All your triggers' popup windows, select clearRange as the Run function.
06. Set the time as you like. (See the example image)

Answer 2

That script should do what you need. You just need to make a couple of modifications.

1. Where is says 'Sheet1' you would use 'Lunch' which is the name of your sheet in your example.
2. In the Script Editor, you can add a new trigger to have the script run every night.
   • In the Script Editor, go to the top menu "Resources » Current Project's Triggers"
   • Click the link "No triggers set up. Click here to add one now."
   • Make sure you set these values:
   • Run » clearRange
   • Events » Time-driven
     • Day Timer
     • Midnight to 1 am

That should run your script every night sometime between Midnight to 1 AM.

If you want an actual menu item that you can click on within the spreadsheet so you can run the script anytime you want, that's a bit more work. Honestly, if that's your primary function ... it's probably just easier to load up the Script Editor from the spreadsheet ("Tools » Script Editor"), and just select the clearRange function and press the play button.

If you do really want to add menu items, there's additional documentation here » https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#addMenu(String,Object). Once you've coded the new menu function, you'll have to update your triggers just like you did to run every night. But instead, you'll change the event type to From Spreadsheet and set the value to On open. That way it'll create the new menu items when the Spreadsheet is opened.