Schedule Google Macros/Scripts - Automatically in the cloud for free!

Google Sheets is a perfect modern and easy alternative to Excel. If you want to schedule a spreadsheet task to be automatic, I recommend you first look into this alternative before using Excel and Task Scheduler.

Two main reasons:

  1. Modern scripting language and editor

  2. Cloud - means your computer can be off and is reliable. No random crashes.

 

Steps

1. Build the Sheet

2. Build the Script

3. Set the Triggers

4. Done

 

 

1. The Objective

In this tutorial, you will learn how to setup a script that copies and emails data, and trigger it to fire at a specific time. I have personally used this a lot.
I give someone a tablet for data entry, they finish for the day, and I get the results emailed to me at night. It’s fantastic.

If you master this, you can automate tasks and truly forget about them due to the Google machine reliably churning in the background.

 

2. Building The Sheet

  1. Create a blank Google Sheet - name the sheet anything you like https://docs.google.com/spreadsheets/

  2. Create two sheets “Data Entry” and “Data Storage”.

  3. Go to sheet “Data Entry”

    1. Add the date to C2

    2. Add headers to C3 and C4 (“Hour” and “Sales” in my case)

    3. Format table as you see fit, but to follow along exactly make sure it is C4:D12 excluding headings.

    4. Add a heading to B3 called “Hidden Columns”, fill the cells down to B12 with $C$2. This will be hidden, but when we copy the data to “Data Storage”, I like to do this so it is easy to index the data for historical reports - especially if you have 1000s of entries.

You should have something similar to the below:

Google Sheet Data Entry.png

Feel free to deviate from the tutorial in regards to the design - all you will have to do is change the ranges around later in the script. Which is no problem.

My idea for the above is to have someone enter into column D. The data will then be copied and cleared from the form at the end of every day.

3. Copy Script

  1. Tools - Script Editor. This will open a new tab.

2. Top Left, name the script anything of your choosing.

3. Delete all the current code, and copy in:

function Copy_to_Storage_then_Delete_Data(){
  
//Define Variables  
var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data Entry');//Your Input Sheet
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data Storage');//Your Storage Sheet
//Copies Data
sheet1.getRange('B4:D12').copyTo(sheet2.getRange(sheet2.getDataRange().getHeight() + 1, 1),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false) //You only need to edit the range. 
//Delete Data
sheet1.getRange("D4:D12").setValue("") //Deletes Data for the new day! Change this depending on your range
}

4. After changing any ranges or sheet names (if you are copying the tutorial exactly, you will be fine), click the “Debug” button right next to the play button.

5. Follow the prompts to approve authorization. This is overkill by Google.

6. Now click the play button. Your data should be copied and deleted! Run it again, and your new data is copied under the last! (you can also format the Storage page if you care)

Data.png

4. Trigger Copy Script

  1. This is very easy! Go back to the Google Script page.

  2. Next to the play icon, click the clock icon “Current project’s Triggers”

  3. Click on “No triggers set up. Click here to add one now.”

4. Leave everything to default, but play around with the “Hour Timer” and “Every hour” box as desired. I changed mine to day timer, 6pm to 7pm.

Trigger Timer.png

5. Click Save, and then save the script as well. You are now all done. Easy as that.
(Note - trigger time doesn’t change timezone to match your country, so you just have to have a play around if you are outside the US).

5. Email Range

  1. Head back to Google Scripts.

  2. Click on Resources-Libraries

  3. We need to add something that changes our range to strings and make it HTML friendly, this cool library “SheetConverter” does it for us. Copy in 1kL7pHe3Ru9hCcc1YXJTdPCuPRo-rDzH9fi3--QF8TE7AxIrQoJXZht8A and click Add

  4. Change to highest version (I’m using 6) and click save.

5. Now we need to add in a function that uses the library we just added to change our range to HTML, then send the email. Copy in the following code, which includes the copy code we created above. 

function convSheetAndEmailM(rng, email, subj)
{var HTML = SheetConverter.convertRange2html(rng);
MailApp.sendEmail(email, subj, '', {htmlBody : HTML});}


function Copy_to_Storage_then_Email_then_Delete_Data(){

//Define Variables  

var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data Entry'); //Your Input Sheet

var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data Storage');//Your Storage Sheet

//Copies Data

sheet1.getRange('B4:D12').copyTo(sheet2.getRange(sheet2.getDataRange().getHeight() + 1, 1),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false) //You only need to edit the range. 

//Email

  var range = sheet1.getRange('C2:D12')//Review the range

  var emailUser = 'test@gmail.com';//Change to email you want to send to

  var subject = 'Daily Sales';//Change to subject

  convSheetAndEmailM(range, emailUser, subject);//Converts to HTML and sends email though your Google Account 

//Delete Data

sheet1.getRange("D4:D12").setValue("") //Deletes Data for the new day! Change this depending on your range

}

6. Click “Run” - ”Run Function” and run the copy script. You will have to review permissions again.

7. Assuming you have changed the script to your email, you should now get an email after you run it!

The format conversion is never perfect, but it does a good enough job for most of my uses. Don’t forget to setup the trigger again if you want to use it.

6. That’s it!

Until I turn it off, my script will be copying data and sending emails indefinitely. It’s easy quick, and robust.
On top of this, there is a ton of practical uses for this platform. Labour tracking, inventory, collaboration, reporting, time keeping to name a few. And it’s all free!

If all your data is on Excel, follow my Excel to Google Sheets tutorial. Also, read about Excel Macro Scheduling!
Another cool use of the above, it to make an alert system that monitors your shares/stocks/etfs. Check out my Google Sheets Stock Dashboard and see if you can do it!
If you want to automatically get your Google Sheet data to Excel, check out the tutorial Link Google Sheets to Excel.

Have questions about triggers and scheduling? Ask here!

7. Download

More templates