Difficulty: Minimal Code
Cost: Free
Time: 5 minutes
If you’ve gone through the trouble of making a Power Automate email or text automation, you probably don’t want to waste time clearing the sheet each day to ensure it doesn’t double message parents.
With a few lines of simple code, and the addition of a button, you can clear hundreds of cells automatically or with the touch of a button.
Step 1: Make a button:
I prefer to use a rounded box for making buttons, but you can pick whichever shape you’d like your button to be.
Add in the color you want and the text. Make your button say whatever you want. Once you’re happy with it, push “Save and Close.”
Step 2: Write the script
Go to the toolbar again and select Tools > Script editor.
You should start with a blank slate that looks roughly like this. You can name your project in the upper left hand corner, and name the file with the left hand toolbar
- Change the name of the function. I call this function “clear_sheet,” but you can use any name that works for you as long as it doesn’t have spaces or certain special characters.
- Add your variables. Use var to name the variables and services you will be using
- Specify what your function actually does. this function uses clearContent to clear the content from cells F2 through I7 without touching the formatting.
function clear_sheet() {
var app = SpreadsheetApp;
var activeSheet = app.getActiveSpreadsheet().getActiveSheet();
activeSheet.getRange("F2:I7").clearContent();
}Feel free to copy and paste the above code into your editor and adjust it to fit your needs.
As long as the cells you’re clearing are in a contiguous area, use the upper left cell as the first cell in the range, and use the bottom right cell as the last cell in your range.
Step 3: Add triggers
With your code done, it’s time to set a trigger. Use the clock in the far left toolbar to select “Triggers.”
From here, click add trigger to start a new trigger.
To schedule your trigger, select the function you want your trigger to run, and use Time-driven as your event source.
Day timer will allow you to set your script to run at a specified time each day. I recommend scheduling the script to run 1-2 hours after any automation which uses the Google Sheet. That way, if the automation takes a while to parse your sheet and run, you have some padding.
Check over your work, select whether you’d like to be notified of errors, and click done.
As an extra layer of utility, add the script to your button. There are three dots in the upper right hand corner of the button. They may be difficult to see, but when you click them, the option to assign a script to the button should show.
Type in the name of your script then press okay.
Google may prompt you saying that they are not sure if the script is secure. If you trust yourself, you can ignore this.
Now you’re done! Test your button and the trigger.
The Automated Teacher 