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
The first step is defining the inputs and outputs. What data do you need to feed into the script and what needs to happen?
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
Why You Should Test Target ROAS Bidding for Shopping Campaigns
The case for testing Target ROAS bidding for AdWords shopping campaigns, plus tips on how to successfully set up such a test.
10 New Ways to Optimize Paid Search with Call Intelligence
Mobile search drives billions of calls to business each year, and calls convert at a higher rate than digital leads. When properly optimized, calls can have a transformational impact on your bottom line. Join this webinar to learn tactical tips and smart strategies to boost...
Google Adds New Tools to Highlight Physical Store Locations
Buying in-store is still a big deal for shoppers, and Google has announced a few new tools to let physical retailers target those shoppers heading in stores.
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
Spend Smarter: Identifying Account Opportunities
Explore a method for projecting performance that makes it easier to provide solid budget increase recommendations and also alleviates some stress.
Up and Running with AdWords Add-on for Google Sheets
In this article we will set up for the AdWords add-on for Google Sheets. By the end you'll set up your own reports and automate the data gathering portion of your workflow.
Tackling Facebook Ad Disapprovals
Why your Facebook ad is disapproved and what you can do to fix it and avoid future problems.
Load More »