How Do I Get Google to Stop Reading My Searches Out Loud
What is Google Apps Script?
Google Apps Script is a cloud based scripting language for extending the functionality of Google Apps and building lightweight cloud-based applications.
What does this mean in exercise?
Information technology means you write minor programs with Apps Script to extend the standard features of Google Workspace Apps. It's great for filling in the gaps in your workflows.
For example, I used to exist overwhelmed with feedback from my courses and couldn't respond to everyone. Now, when a pupil submits their feedback, my script creates a typhoon email in Gmail ready for me to review. It includes all the feedback so I can read it inside Gmail and respond immediately.
It fabricated a previously impossible task manageable.
With Apps Script, you tin can do cool stuff like automating repeatable tasks, creating documents, emailing people automatically and connecting your Google Sheets to other services you use.
Writing your kickoff Google Script
In this Google Sheets script tutorial, we're going to write a script that is spring to our Google Canvas. This is called a container-jump script.
(If you're looking for more advanced examples and tutorials, check out the full list of Apps Script articles on my homepage.)
How-do-you-do Earth in Google Apps Script
Let's write our first, extremely basic program, the classic "Hello globe" program dearest of computer pedagogy departments the world over.
Begin by creating a new Google Sheet.
Then click the menu Tools > Script editor... to open up a new tab with the code editor window.
This will open a new tab in your browser, which is the Google Apps Script editor window:
Past default, information technology'll open up with a single Google Script file (code.gs) and a default lawmaking block, myFunction():
function myFunction() { } In the lawmaking window, between the curly braces after the function myFunction() syntax, write the following line of code so you accept this in your code window:
function myFunction() { Browser.msgBox("Hello World!"); } Your lawmaking window should now look like this:
Google Apps Script Authorization
Google Scripts have robust security protections to reduce take chances from unverified apps, and so we go through the potency workflow when we commencement authorize our own apps.
When you hit the run button for the get-go time, you will be prompted to authorize the app to run:
Clicking Review Permissions pops upwardly another window in plow, showing what permissions your app needs to run. In this instance the app wants to view and manage your spreadsheets in Google Drive, so click Allow (otherwise your script won't be able to collaborate with your spreadsheet or do anything):
❗️When your first run your apps script, you lot may see the "app isn't verified" screen and warnings about whether you want to continue.
In our instance, since nosotros are the creator of the app, we know it's safe so we do want to continue. Furthermore, the apps script projects in this post are non intended to be published publicly for other users, so nosotros don't need to submit it to Google for review (although if yous desire to practise that, here's more data).
Click the "Advanced" button in the bottom left of the review permissions pop-up, and then click the "Get to Starter Script Code (unsafe)" at the lesser of the next screen to go on. Then type in the words "Continue" on the next screen, click Next, and finally review the permissions and click "Let", equally shown in this image (showing a different script in the old editor):
More information can be found in this detailed web log mail from Google Developer Proficient Martin Hawksey.
Running a part in Apps Script
One time you've authorized the Google App script, the function will run (or execute).
If anything goes wrong with your lawmaking, this is the stage when yous'd see a alert bulletin (instead of the xanthous message, you'll become a red box with an error message in it).
Render to your Google Sheet and you lot should see the output of your program, a message box popup with the classic "Hello world!" message:
Click on Ok to dismiss.
Slap-up job! Yous've now written your beginning apps script programme.
Rename functions in Google Apps Script
We should rename our function to something more meaningful.
At nowadays, it's called myFunction which is the default, generic name generated by Google. Every time I want to call this part (i.e. run it to practise something) I would write myFunction(). This isn't very descriptive, so let's rename it to helloWorld(), which gives us some context.
Then change your lawmaking in line ane from this:
office myFunction() { Browser.msgBox("Hi World!"); } to this:
function helloWorld() { Browser.msgBox("Hullo World!"); } Note, it'south convention in Apps Script to use the CamelCase naming convention, starting with a lowercase alphabetic character. Hence, we name our function helloWorld, with a lowercase h at the showtime of hello and an uppercase West at the start of World.
Adding a custom carte in Google Apps Script
In its current class, our program is pretty useless for many reasons, not to the lowest degree because we can but run it from the script editor window and not from our spreadsheet.
Let's set up that by calculation a custom card to the menu bar of our spreadsheet so a user can run the script within the spreadsheet without needing to open the editor window.
This is actually surprisingly easy to do, requiring only a few lines of code. Add together the following 6 lines of code into the editor window, above the helloWorld() function we created above, as shown hither:
function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu('My Custom Carte') .addItem('Say Howdy', 'helloWorld') .addToUi(); } function helloWorld() { Browser.msgBox("Hello Earth!"); } If you lot look back at your spreadsheet tab in the browser now, aught volition have inverse. You lot won't accept the custom menu there yet. Nosotros need to re-open up our spreadsheet (refresh it) or run our onOpen() script get-go, for the card to bear witness upward.
To run onOpen() from the editor window, offset select so run the onOpen function equally shown in this image:
Now, when you return to your spreadsheet you'll meet a new carte on the right side of the Help option, called My Custom Menu. Click on information technology and information technology'll open upwardly to show a pick to run your Howdy World program:
Run functions from buttons in Google Sheets
An alternative fashion to run Google Scripts from your Sheets is to demark the role to a push button in your Canvass.
For case, here's an invoice template Canvass with a RESET button to articulate out the contents:
For more information on how to practise this, accept a look at this mail service: Add A Google Sheets Push button To Run Scripts
Google Apps Script Examples
Macros in Google Sheets
Another great fashion to get started with Google Scripts is by using Macros. Macros are pocket-size programs in your Google Sheets that you record so that you can re-utilize them (for example applying standard formatting to a tabular array). They use Apps Script under the hood then information technology'south a smashing manner to go started.
Read more than: The Consummate Guide to Simple Automation using Google Sheets Macros
Custom role using Google Apps Script
Let'southward create a custom function with Apps Script, and also demonstrate the use of the Maps Service. We'll be creating a modest custom function that calculates the driving distance between two points, based on Google Maps Service driving estimates.
The goal is to be able to have 2 identify-names in our spreadsheet, and type the new office in a new cell to get the distance, equally follows:
The solution should be:
Copy the following lawmaking into the Apps Script editor window and save. Start time, you'll need to run the script once from the editor window and click "Allow" to ensure the script can collaborate with your spreadsheet.
office distanceBetweenPoints(start_point, end_point) { // get the directions const directions = Maps.newDirectionFinder() .setOrigin(start_point) .setDestination(end_point) .setMode(Maps.DirectionFinder.Mode.DRIVING) .getDirections(); // go the showtime route and return the distance const road = directions.routes[0]; const distance = route.legs[0].distance.text; render distance; } Saving data with Google Apps Script
Let'due south take a await at another simple employ case for this Google Sheets Apps Script tutorial.
Here, I've setup an importxml function to extract the number of followers a specific social media aqueduct has (e.m. in this case a Reddit channel), and I want to save copy of that number at periodic intervals, similar and then:
In this script, I've created a custom card (as nosotros did above) to run my main office. The principal function, saveData(), copies the superlative row of my spreadsheet (the alive data) and pastes it to the adjacent blank line below my electric current data range as text, thereby "saving" a snapshot in time.
The code for this example is:
// custom menu part function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu('Custom Bill of fare') .addItem('Save Data','saveData') .addToUi(); } // function to relieve information part saveData() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const canvass = ss.getSheets()[0]; const url = canvas.getRange('Sheet1!A1').getValue(); const follower_count = sheet.getRange('Sheet1!B1').getValue(); const appointment = canvass.getRange('Sheet1!C1').getValue(); sail.appendRow([url,follower_count,engagement]); } See this mail: Saving Data in Google Sheets, for a step-by-stride guide to creating and running this script.
Google Apps Script example in Google Docs
Google Apps Script is by no means confined to Sheets merely and can exist accessed from other Google Workspace tools.
Here's a quick example in Google Docs, showing a script that inserts a specific symbol wherever your cursor is:
We practice this using Google App Scripts as follows:
1. Create a new Google Doc
2. Open script editor from the carte du jour: Tools > Script editor...
3. In the newly opened Script tab, remove all of the average code (the "myFunction" lawmaking cake)
4. Copy in the following code:
// code to add together the custom menu role onOpen() { const ui = DocumentApp.getUi(); ui.createMenu('My Custom Bill of fare') .addItem('Insert Symbol', 'insertSymbol') .addToUi(); }; // code to insert the symbol function insertSymbol() { // add symbol at the cursor position const cursor = DocumentApp.getActiveDocument().getCursor(); cursor.insertText('§§'); }; 5. Y'all can change the special character in this line
cursor.insertText('§§');
to whatever you want it to exist, due east.g.
cursor.insertText('( ͡° ͜ʖ ͡°)');
half-dozen. Click Save and requite your script project a name (doesn't bear on the running and then phone call it what you want east.g. Insert Symbol)
7. Run the script for the first time past clicking on the card: Run > onOpen
8. Google will recognize the script is not all the same authorized and enquire you if yous want to continue. Click Proceed
nine. Since this the commencement run of the script, Google Docs asks you to qualify the script (I called my script "examination" which you can see below):
10. Click Allow
11. Return to your Google Dr. now.
12. You lot'll have a new menu selection, then click on it:
My Custom Carte du jour > Insert Symbol
thirteen. Click on Insert Symbol and you should see the symbol inserted wherever your cursor is.
Google Apps Script Tip: Use the Logger course
Use the Logger class to output text messages to the log files, to assist debug lawmaking.
The log files are shown automatically later the plan has finished running, or past going to the Executions menu in the left sidebar menu options (the fourth symbol, under the clock symbol).
The syntax in its virtually basic grade is Logger.log(something in hither). This records the value(due south) of variable(s) at different steps of your program.
For example, add together this script to a code file your editor window:
function logTimeRightNow() { const timestamp = new Date(); Logger.log(timestamp); } Run the script in the editor window and you should see:
Real world examples from my own work
I've but scratched the surface of what'south possible using G.A.Due south. to extend the Google Apps experience.
Here'southward a couple of interesting projects I've worked on:
1) A Sheets/spider web-app consisting of a custom web form that feeds data into a Google Sheet (including uploading images to Drive and showing thumbnails in the spreadsheet), then creates a PDF re-create of the data in the spreadsheet and automatically emails it to the users. And with all the information in a primary Google Canvas, it's possible to perform data analysis, build dashboards showing data in existent-time and share/collaborate with other users.
ii) A dashboard that connects to a Google Analytics business relationship, pulls in social media data, checks the website status and emails a summary screenshot as a PDF at the end of each solar day.
3) A marking template that can send scores/feedback to students via email and Slack, with a single click from inside Google Sheets. Read more in this article: Save fourth dimension with this custom Google Sheets, Slack & Email integration
My own journey into Google Apps Script
My friend Julian, from Mensurate School, interviewed me in May 2017 nearly my journey into Apps Script and my thoughts on getting started:
Google Apps Script Resources
For further reading, I've created this list of resources for information and inspiration:
Course
Documentation
Official Google Documentation
Google Workspace Developers Blog
Communities
Google Apps Script Group
Stack Overflow GAS questions
Imagination and patience to learn are the just limits to what you can do and where you tin can become with GAS. I hope y'all feel inspired to try extending your Sheets and Docs and automate those boring, repetitive tasks!
Related Manufactures
How Do I Get Google to Stop Reading My Searches Out Loud
Source: https://www.benlcollins.com/apps-script/google-apps-script-beginner-guide/