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

Gruchot

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)

Goldbrick

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.

Brana

Thanks so much for your reply. I was able to create the trigger you suggested to the script function. Now on the spreadsheeet side, I went to "Tools- Script Manager". I see the script I made, and selected the "Run" button. Question- will this run only one time when I click the button, or is it now associated and connected to the spreadsheet to run the trigger that I set? – Gruchot

@Gruchot If you see the trigger in your "Current Project's Triggers" it should run every night. When you click "Run" that just is an ad hoc run. So it won't disable any of the automated triggers. – Brana

Answer 3

It appears the currently upvoted answers were right at one point in time, but they are both from 2014 and 2018 and it appears the API for Google Sheets has since been updated along with how you accomplish this through the UI of the spreadsheet you're working on. Listed below is how I accomplished the task that the original question was asking about. I will try to communicate the instructions on how to do this in a way that a person with little to no software engineering or programming experience can follow them.

It appears the getSheetByName() function either no longer exists or is no longer necessary. To do what the original author of this question is trying to do (as of April 2nd, 2023), you should use the following code:

function clearRange() {
      // replace '1uHKL0nc0qi6j8zW9aoG6Je67nmUu0_pqVQdwWYMuI9w' with your actual sheet ID

      var sheetActive = SpreadsheetApp.openById("1uHKL0nc0qi6j8zW9aoG6Je67nmUu0_pqVQdwWYMuI9w");
      sheetActive.getRange("A:Y").clearContent();
    }

Replace the second instance (or both instances if you so wish) of 1uHKL0nc0qi6j8zW9aoG6Je67nmUu0_pqVQdwWYMuI9w in the code listed above with the value that's underlined in red in the image below for the Google Sheet you're trying to run the code on. If you want to clear a range of columns different than A:Y, change the A:Y listed between the quotations in the script above to the values of the columns you'd like to clear instead. See the following examples:

-To clear all columns from F to M:

F:M

-To just clear column B:

B:B

Then, navigate to the Extensions tab and then select the Apps Script option:

Select the Editor button from the top-left menu:

Delete the current contents already in the Editor. It should look something like this:

Add the contents of the script that I provided towards the top of this post into the editor like below. Note that the Sheet ID value should be swapped out for the sheet ID of the Google Sheet you're attempting to run the script on.

Then hit the save button:

Select the Triggers button from the top-left menu:

Select the Add Trigger Button in the bottom-right corner:

You can then select the options that make the most sense for you. To do what the original question was asking (clear values every day at midnight), you'd want to select the following options and then hit the save button in the bottom-right corner:

Note that if you see an error whenever you try to hit the save button in the previous step, you likely just need to enable access to your spreadsheet/account through the pop-up menu. Firefox was blocking my pop-up menu, so if you don't see anything when saving and you're seeing an error, disable your pop-up blocker.

If this was successful, you should see something like this populated in the Triggers page:

If you see this, congrats your spreadsheet will now automatically clear the values of the rows specified in the script (A to Y in the example I provided) between 12 and 1 am CST every night!

Internationalism