save hours of time google apps script

Learn to Code

Tim Cook from Apple says it’s better to learn coding than a second language, maybe maybe not. In a connected world, I think knowing more than one language is pretty darn important. Coding is definitely a modern skill that is valuable for our students to learn but the reason we should learn to code is it will save us hours of time!

Repetitive Stuff Should Be Done by Robots

We are teachers, we need to do repetitive tasks all the time due to the fact that we have more than one student. Knowing some basic coding can help you make light work out of repetitive tasks.

Pull the Paragraph

My English teacher husband assigned his students to write a paragraph into a Google Doc. The only problem is this requires opening 140 Google Docs to read them and provide feedback. Time consuming. Instead, I spent TWENTY MINUTES coding a Google Sheets spreadsheet to pull the text from each Google Doc and put it into the spreadsheet so he could read all 140 papers without opening up any of them. He was then able to put specific feedback into column C and push those comments to each student’s paper, again without opening any of them.

alicekeeler.com/pulltheparagraph

Google Apps Script – Code Google Docs

The first thing to know is that you can code Google Docs, Sheets, Slides, Forms, Maps, etc… You can add code to your documents by using the Script Editor. Access this using the Insert menu. On Google Forms, you would access the script editor by clicking on the 3 dots.
Script Editor

Multiple Choice

Something I like about coding with Google Apps Script is that it is multiple choice. It provides me suggestions for the different methods I might want to use.


(adsbygoogle = window.adsbygoogle || []).push({});

Try This

Try a little Google Apps Script to get you started. You’ll want to do some actual tutorials, but really Google Apps Script is very accessible. You can do a lot with knowing very little.

Add Sheets to a Spreadsheet

The very first thing I coded in Google Apps Script was to take a roster of student names and create a tab for each student within the same spreadsheet. I called this RosterTab. I was able to be successful coding this idea after only 1 hour of being introduced to Google Apps Script. I had completed a very light tutorial on JavaScript to get me started. In this activity, you will do something a little more basic since I will assume this is your very first attempt at trying to code with Google Apps Script.

Create a Spreadsheet

Start a new Google Sheets spreadsheet.

Name Your Spreadsheet

Replace the default “Untitled spreadsheet” with whatever you want.

Insert Menu

Use the Insert menu and choose “Script editor.”

Script Editor

This takes you to the edit screen where you can edit your code. Your code goes within a function. Notice that Google Apps Script has provided you with a default function to get started with. The function is called myFunction().
Function

Curly Braces

Your code will go between the curly braces. I like to push enter a few times to give me more space between the curly braces.
Space between the curly braces

What App Are You Using?

Your first line of code will identify which app you are using. App being, Google App. In this case, we are using a Google Sheets spreadsheet so we are using SpreadsheetApp.

Other apps you might use are DocumentApp, FormApp, SlidesApp, MapsApp, CalendarApp, etc…

Define a Variable

It is a good idea to define a variable when writing a line of code. This allows you to reuse that line of code without retyping the code. A shortcut so to speak. I like to use the same variables that the Google Apps Script Developers site uses so that when I look things up I do not get confused. For a spreadsheet, use the variable ss to indicate SpreadsheetApp.

var ss = SpreadsheetApp

var is the shortened version of the word variable. So var ss says my variable ss is equal to….

Press Period

After typing SpreadsheetApp press the period key to bring up a multiple choice list of options for what you can do with SpreadsheetApp.
Multiple choice list of things to do with spreadsheetapp

Some methods of interest….

  • create() : Create a new spreadsheet, I use this a lot!
  • getUi() : Get the User Interface.
  • openById() : Reference another spreadsheet by using the spreadsheet ID.
  • openByUrl(): Reference another spreadsheet by using the spreadsheet URL.

Keep those in mind, they are super handy for when you want to code your own idea for a project. But, the one I use most often is

var ss = SpreadsheetApp.getActiveSpreadsheet();

This means… use THIS spreadsheet.


(adsbygoogle = window.adsbygoogle || []).push({});

Semicolon

After each line of code you want to have a semicolon. Add one now!
Add a semi colon to the end of the spreadsheet

Next Line

Start the next line with the variable that you just used. You want to build on this. What do you want to do with that active spreadsheet?

Press Period Again

Type the variable ss and then press the period to show a multiple choice list of options of what you can do with the active spreadsheet. Now this is a really interesting multiple choice list. Read it slowly so you know what things are possible. If you don’t know what it is, skip it, it isn’t something you want to do 🙂
What can you do with the active spreadsheet

InsertSheet()

Find the method InsertSheet().
insertsheet


(adsbygoogle = window.adsbygoogle || []).push({});

Don’t forget you need a semicolon at the end of the line of code.
semicolon at the end of the line of code

Save and Run

Press the Save icon in the toolbar (or Control S). You will be prompted to save your script. It doesn’t matter what you name it. Then look for the Run icon, it looks like a sideways triangle. Run myFunction.
Save and run the code

Authorize

You will be asked to authorize the script. It may look scary but it is simply giving yourself permission to allow the script to access your spreadsheet.
Click on advanced

click on unsafe

Check the Spreadsheet

The Script editor opened in a new tab, go back to the tab with the spreadsheet. Notice you now have TWO sheets in your spreadsheet! You coded that to happen!
You coded this second tab

Run it Again

Go back to the tab with the Script editor and press the Run button again. Every time you press the Run button it will add a new sheet to the spreadsheet.

Copy and Paste the Code

If you want to insert 10 sheets you could press the Run button 10 times but then you could also just press the add Sheet button in the spreadsheet 10 times so you’re not saving any time. Highlight the line of code that inserts a sheet and paste it as many times as you want. Then Save and press the Run button.
insert 10 sheets

Not impressed? Okay, remember this is supposed to be a first attempt at coding Google Apps Script, nothing fancy! And honestly, you would never paste the same line of code 10 times. Instead, you would code a loop. Loops are where coding gets exciting and saves you as a teacher hours of time. But that is not for today’s lesson. Just remember, you would like to learn how to do a loop.

Name the Sheets

In the parenthesis for each of the insertSheet() methods you pasted you can name the tab you are inserting.

All text strings need to be in quotations.

Type a single quote and then what you want to name the tab and end it in a single quote. This defines a text string. It should change to red.
Name the tabs in single quotations

Save and Run. Now you should have a bunch of tabs in your spreadsheet.
look at all those tabs you made!

Note: spreadsheets don’t like tabs with the same name. If you try it again make sure you change the tab names in the code.

One More Notch to Kick it Up

Delete the ss.insertSheet() methods. Make a new one. This time use a comma to indicate what tab spot you want to place the sheet. By default, the new sheet will be added to the end of the sheets. However, what if you want the sheet to be the 5th sheet? Put comma and then a number to let the script know where to insert the sheet.

Note: JavaScript starts counting at zero. So if you want the 1st sheet you need to choose 0. If you want to insert at the 5th sheet you need to choose 4. 

Add a sheet number

Save and Run.
Add some more tabs

Success!

I hope that was fun and you were successful! Now that you know you can code Google Apps, think about what repetitive things you’re doing and how you might use Google Apps Script to save you hours of your life!

For more tutorials visit
http://www.alicekeeler.com/scripts/apps-script-tutorials/


(adsbygoogle = window.adsbygoogle || []).push({});

Digiprove sealCopyright secured by Digiprove © 2017