Using Google Sheets as a Script Controller
September 21, 2017
Today we’re going to use Google Sheets as a control center of sorts for AdWords Scripts. The Sheets interface allows users to make adjustments via a spreadsheet rather than through the code itself. This method not only makes changes easier to implement but empowers less technical users to harness the power of scripts.
In this article, we’ll cover an example use case then go through a generalized example you can use in your own accounts. Although there won’t be a complete script there will be pointers throughout the article if you are inexperienced with scripts but want to try this yourself.
Imagine a scenario where you have a team making many routine changes based on quickly changing feedback
In an extreme case this could be current events in which you want to make sure ads are showing based on changing external factors. You may increase or decrease bids by different ratio’s depending on marketing needs and recent trends.
We had a client in a similar situation. While building a dashboard to monitor this work, Jacob Brown asked if there was a way we could use that information to make changes to the account. Removing some of the tedious account editing would speed up the team’s workflow and reduce stress.
How To Approach the Setup
Then you need to define what you want to do. This could be any action such as enabling or pausing an entity or changing max CPCs.
What to Include
A standardized account structure helps here as you can select only the pieces you need by aspects such as campaign name or keyword text. Labels work too if your structure is a mess or you need fine toothed selections.
In a later section, we’ll use this information we’ll build these selectors into our script.
What to Change
Now that we know what to change we need to define how we’ll change it. For this example, we will cover bidding. Let’s assume that we have a few bidding thresholds for all affected keywords.
We can assign these different levels such as aggressive, balanced, and hyper aggressive.
Balanced bids can be set to $2.00, aggressive can be set to $4,00 and hyper-aggressive can be set to $6.00. This isn’t always the best way. You can use percentages or different bid levels for each keyword selection as well but I’ll skip over that for simplicity’s sake.
Setting Up the Sheet
In the last section, we defined what needs to change and how we’ll change it. Now we need to put that information in a sheet so the script can access it.
We will set up two tables in the sheet. One for input targets and their bid aggression levels and another for bid amounts.
To enforce standard inputs, you can use data validation to control inputs. That way someone putting “Agggressive” does not get passed over because the script is looking for “Aggressive.”
Now we are done with the inputs, let’s handle the script!
Setting Up the Script
You’ll need a little bit of coding knowledge or confidence to tackle this next part. It’s nothing too intense though. All we have to do is get the keywords based on the table and make bid changes based on the data in the other table.
The steps are,
- Load and select the appropriate data from the sheet.
- Select keywords and bid levels based on data in the tables.
- Apply changes to those keywords.
First we’ll load the spreadsheet via openByURL(). If you are using the same sheet for other tasks, such as reporting, you can also select by sheet name.
Then we’ll need to select the data via getRange().getValues(). This will pull the values from this range of cells. Pulling the value of the cells means you can use to grab the output of formulas in a selected cell.
This will return a list of values. Use Logger.Log() if you’d like to see what it contains.
If you have it set up like the example in the article, it’ll return the rows in this format [keyword/theme, bid aggression].
Then you need to select your keywords. Below is an example function for returning a selection of keywords. We’ll use the function later to run through a list of keyword pulls.
We can use a loop to move through that piece by piece. For each item in the list, i, we will select [i] for the keyword/theme, and [i] for the aggression level. The variable i stands in as a place holder and the two pieces we need will always be at index 0 and 1.
Then we loop through each entry, use our keyword selector function to pull the keywords and apply the appropriate bids.
The last step is to set the script schedule. This can be a weakness as the scheduler runs as frequently as one hour. This means changes are not immediate but will happen within an hour.
We moved through the script portion quickly. Each approach will vary by the clients account set up but we hit the crucial points.
Alternatively if you’d rather not handle the bid changes and selectors in the script you can modify those directly in Google Sheets and use newCsvUpload() to do an automated bulk upload. Check out Mitch Larson’s presentation, Using AdWords Scripts to Create Your Own Ad Tech Landscape, for more details.
Browse By Category
The Advertising Principles That Guide Facebook's Decisions
Facebook highlights the advertising principles that guide their decision making in response to recent events.
Smart Ways to Get More Marketing Budget and Better Tools
In this new live webinar, Kristin Vick from Hanapin Marketing and Jeff Sauer from Jeffalytics discuss how marketers can ensure they have the budget they need to be effective with online advertising and get the right tools to make that argument.
The Hero Conf Difference
Of the 50+ search and social conferences you can choose from, we're here to show you why Hero Conf is a can't miss event for busy digital marketers like you.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Improve Your Facebook Ads With Creative Hub and Split Testing
With the updates to Creative Hub and the Split Test feature, one can now institute a basic ad creation and testing process directly within Facebook.
Work Habits & Resources for the Stressed-Out PPC Manager
Feeling a bit stressed this holiday season? Get tips and resources that will help you become more efficient and alleviate stress!
A 180 of Google Analytics 360
The enterprise version of Google Analytics offers several paid search perks including, advanced data integrity, integrations and reporting.