“Automate & extend Google Workspace with simple code. Apps Script is a cloud-based JavaScript platform that lets you integrate with and automate tasks across Google products.”
Source: Google Workspace
This is the first thing you read in the Google Apps Script documentation. Sounds promising, especially the part about simple code. In fact, the creators of Apps Script set out to build a tool for ‘non-traditional programmers’.
In my experience, Google kept its promise to create a coding environment that empowers ‘citizen developers’ – like you and me – to automate workflows without the steep learning curve of a classic education in programming.
Do you frequently work with Google Sheets and the Google Workspace already? Perfect, because with AI you can build these automations faster than ever. Simple scripts are one of the top use cases of ChatGPT. Still, you cannot take for granted that the output will be flawless.
ChatGPT can and will be wrong – and having a basic understanding of coding helps immensely to figure out when and where it goes off the rails.
How to use ChatGPT to write Apps Scripts for SEO automation
The main goal of this two-part article is to give you a practical walkthrough about how to write a Google Apps Script from scratch together with ChatGPT. Right now, you’re reading the first part focusing on preparing a good coding prompt for accessing an API, while the second part is all about actually creating the script together with ChatGPT.
Our example use case will be to access the DataForSEO SERP API, to extract some specific data and to write it to a Google Sheet in our preferred format. As errors are a natural part of coding, I won’t just give you the final, polished script but also show you how to debug in the Apps Script environment if something doesn’t go as planned.
To make this article easier to digest, I documented the different stages of the Apps Script on GitHub and also made the entire exchange with ChatGPT public:
- GitHub repository incl. ReadME with different script versions and the final Apps Script.
- All ChatGPT prompts and answers (using version 3.5 on purpose).
The ReadME file documents a full breakdown of the steps that I took to arrive at the final script – including the mistakes and all the extra back and forth that I had to do with ChatGPT to get the desired output.
Please note: You likely won’t be able to completely reproduce the same ChatGPT answers. It could be that ChatGPT doesn’t make the same mistakes that it did for me. Especially if you use the newest version GPT-4o, you might reach your goal faster.
FAQs about Google Apps Script, APIs and DataForSEO
Before we get started with prompting and scripting, please skim through the following questions and answers. It helps to be comfortable with these topics to follow later.
What is an API?
An API (Application Programming Interface) is a set of rules that lets different software applications talk to each other – like a Google Apps Script communicating with DataForSEO. Practically speaking, you use an API by sending data (using an HTTP request) to a specific URL, known as an ‘API endpoint’. The API then processes this request and sends back a response with the information you need.
So in short, an API allows applications to request and receive data from one another. If you want to dive even deeper, DataForSEO has a great beginners’ guide about APIs, what they are and how they work.
What is an API endpoint?
An API endpoint is a specific URL where an API receives requests. It’s the address to which you send your data when making an API call, and it’s where the API sends back its response. Think of it as the exact location within an API where the interaction happens.
This is what one endpoint of the DataForSEO SERP API looks like: https://api.dataforseo.com/v3/serp/google/organic/live/advanced
How can we work with APIs for SEO automation?
The goal of this article is to introduce you to one of the most powerful ways to use Google Apps Script for SEO: to request data from APIs, extract what you need and write it in a Google Sheet in a custom format of your choice.
Some examples:
- Use the Oncrawl API to pull crawl data into your sheet for a custom analysis.
- Use the SurferSEO API to create new content briefings in bulk.
- Use the SEMrush API to get keyword reports in bulk.
These use cases might not be relevant for you, as API access often depends on the tools you use and your subscription type. Some providers, like DataForSEO, offer on-demand pricing, so you only pay for each API request you make without needing a monthly subscription.
What is the DataForSEO SERP API?
The DataForSEO SERP API allows you to get SERP data for specific search queries. You could use it to extract the top 10 rankings or to understand what SERP item types show up for specific queries. If you would like a visual walkthrough, I can recommend this video from Agency Automators: “Learning About How SERPs are Built with Data for SEO.”
Why are we working with the SERP API from DataForSEO?
I chose this API for the walkthrough because SERPs are a hot topic right now, especially with Google’s introduction of AI overviews. Of course, DataForSEO is not the only provider of SERP APIs, but it’s the one that I’m already familiar with – others include SerpApi, VALUE SERP or ScraperAPI.
It will be fascinating to see how these providers handle the shift to AI overviews, i.e. the shift to less stable and more unpredictable SERP data. At the moment, none of them actually capture AI overviews, but that might change sooner or later.
I also liked the idea of using a direct data provider with on-demand credits, as it lowers the barrier to entry to test things out. No need for a subscription, just top up your account when needed.
Do you need a DataForSEO account for authentication?
Yes, you need to create an account with DataForSEO. There are no up-front costs and you get a 1$ balance for testing. This should be enough for 500 requests of the SERP API. So, plenty to help get you started and to test what you need. Please find below the section of the script, where you’ll need to add your email address and password.
var username = ''; // Replace with your DataForSEO username var password = ''; // Replace with your DataForSEO password
How can you monitor your expenses in DataForSEO?
When testing your scripts, monitor your usage stats in the DataForSEO dashboard to see when the API was actually queried (and cost money). You can also set account-wide or API-specific limits to keep the costs in check (see API limits).
Figure 1 – DataForSEO dashboard with SERP API usage statistics
What is Google Apps Script?
Google Apps Script is a “low-code” automation tool that uses JavaScript syntax. Unlike tools with “no-code” options (e.g. SeoTools for Excel or KNIME), Apps Script requires coding, but it’s simple scripting with straightforward logic that ChatGPT can assist you with.
Ideally, you already have some familiarity with the Apps Script environment. You can check out my slides from the Women In Tech SEO festival and Roxana Stingu’s article, where she used ChatGPT for SEO automation with Google Apps Script.
Do you need to know coding to work with Google Apps Script?
No, but it certainly helps. While ChatGPT can assist you, having a basic understanding of JavaScript syntax can be incredibly beneficial. It allows you to better understand where things might be going wrong when ChatGPT seems stuck. This simply increases your success rate when turning your automation ideas into working scripts.
In my conference slides, you’ll find a collection of resources you can use to start learning JavaScript and work in the Google Apps Script environment (see slide 113). But, don’t get stuck in a tutorial loop. Start tinkering right away. Often, you can pick up the syntax along the way, even by asking ChatGPT to explain things to you.
What is JSON-LD?
What’s cool: API responses are usually in JSON-LD format, which you most likely know from adding structured data to a website. And knowing its syntax a bit is helpful to pinpoint the data in an API response that you’d like to extract.
JSON-LD (JavaScript Object Notation for Linking Data) is a common data format for exchanging information between applications in a simple text form. It uses nested key-value pairs to structure data hierarchically. We’ll use JSON-LD to send data to the DataForSEO SERP API and get our responses back in the same format.
When working with APIs, it helps to know how to navigate JSON-LD output and access specific information nested at a particular level.
Pop quiz: Take a look at the JSON example below. How can you get the title of the second result and store it in a JavaScript variable?
var jsonData = {
"response": {
"data": {
"keyword": "best hiking trails",
"country": "US",
"language": "en",
"items": [
{
"title": "Top 10 Best Hiking Trails in the US",
"meta_description": "Discover the top hiking trails in the United States with breathtaking views and stunning landscapes.",
"position": 1
},
{
"title": "10 Must-Visit Hiking Destinations in the US",
"meta_description": "Explore the most scenic hiking destinations across the United States for your next outdoor adventure.",
"position": 2
},
{
"title": "Hiking Trails Near Me: Find Your Next Adventure",
"meta_description": "Find the best hiking trails near you and embark on a memorable outdoor journey.",
"position": 3
}
]
}
}
};
In JavaScript syntax, it works as follows:
var secondResultTitle = jsonData.response.data.items[1].title; console.log(secondResultTitle); // Outputs: "10 Must-Visit Hiking Destinations in the US"
To put things into plain language, with the syntax jsonData.response.data.items[1].title, we tell the machine:
“In the JSON data, locate ‘response’, then ‘data’, then ‘items’. To retrieve the title of the second item, access index 1 in ‘items’ and find its ‘title’.”
You can read more about accessing JSON in the JavaScript documentation.
Preparation: Start coding with ChatGPT
The beautiful thing about ChatGPT is that it becomes less and less important to write every line of code yourself – and that is empowering, especially for simple SEO automations. We don’t need to create full-fledged web applications to make our lives easier.
Often simple scripts with simple logic are already very useful. And these simple SEO automations are a great use case for ChatGPT.
Still, I want to encourage you to learn the basics of JavaScript (see slide 113) as it enables you to understand and debug when ChatGPT goes off the rails.
“Everyone should know how to program a computer, because it teaches you how to think!”
– Steve Jobs
Of course, it is not a must to know JavaScript syntax to start creating Apps Scripts together with ChatGPT. But, coding knowledge will accelerate your progress as it helps you to craft your prompts more carefully and increases the chances of a good output.
Crafting a good ChatGPT prompt to create a Google Apps Script
When creating a prompt, we want to give ChatGPT as much information as possible. That also means that we have to be very clear about what should be done and when.
You are not yet 100% sure about the main steps of your task? Double-check if the task is already a good use case for automation. Start by testing the workflow manually until you have a repeatable list of steps. You can also ask ChatGPT for tips and inspiration on how to transform your idea into an automatable SEO workflow.
So, what do we want our Apps Script to do? To answer this question, it is helpful to be familiar with the concept of control flow in coding, which visualizes how our machine processes a piece of code.
“Control flow in JavaScript is how your computer runs code from top to bottom. It starts from the first line and ends at the last line, unless it hits any statement that changes the control flow of the program such as loops, conditionals, or functions.”
– Source: Medium.com
Tell ChatGPT what the script should do in a similar fashion. Then it will have an easier time translating this into coding syntax. A very simple prompt template following this idea could be:
“Write a Google Apps Script that does […] & uses […] as input. It gets the input from […]. The input should be transformed like […]. The output should be written to […]. Here is an example of the input & output: […].”
Prompting ChatGPT to access the DataForSEO SERP API
Now, let’s enrich this generic template with more custom information about how to connect to the DataForSEO SERP API. First, think about the control flow and what we want to do. Connecting to an API basically means:
- We send data to an API endpoint (i.e. to a URL) with an HTTP request.
- And ideally, we directly get our result data back right away in the HTTP response.
Let’s ask ourselves some more questions for our use case:
- What data do we want? We want to keep it simple and extract data about the item types (like “Local Packs” or “PAA”) that are included in a specific SERP.
- What input do we need? Without checking the API documentation, we likely need to provide (1) the search query, (2) the SERP country, (3) the SERP language and (4) the search device.
- From where does the Apps Script get its input? That’s up to us. I’ve set up a template in the “DataForSEO Settings” tab in the example Google Sheet I shared during my WTSFest talk . Here, you can pick the country, language, and device and also add a list of keywords. DataForSEO works with “country_codes” and “language_codes” which can be more precise than selecting names. In columns E-N, I already filled in the latest country/language information (see docs). Based on this, the correct codes are assigned with a formula in cells C2 & C3.
- Where does the Apps Script write its output? Let’s say we simply want to write the info about the SERP item types in a Google Sheet with the columns: Keyword, SERP Country, SERP Language, Search Device, SERP Items. This is prepared in the example sheet in the tab “DataForSEO Output”.
With this information in mind, we can customize our prompt template a lot already:
“Write a Google Apps Script that gets SERP data from the DataForSEO SERP API. It uses (1) the search query, (2) the SERP country code, (3) the SERP language code and (4) the search device as input.
It gets the input from the Google Sheets tab “DataForSEO Settings”. A list of search queries can be added with variable length in column A6:A. The SERP country code is set in cell C2, the language code in cell C3 and the search device in cell C4.
The output should be written to the sheet “DataForSEO Output” with the following columns: Keyword, SERP Country, SERP Language, Search Device, SERP Items.
The SERP items should be listed comma-separated in the respective cell.”
Using API documentation to craft prompts for ChatGPT
We could try our luck with this in ChatGPT, but let’s enrich our prompt even more by providing some examples. But how can we do that if we want to connect to the API for the first time?
The beauty of APIs is that they already come with documentation. The documentation provides details about what the API endpoints can do and examples of how you can communicate with them.
It’s time to dive into the DataForSEO SERP API documentation. On the general landing page of the SERP API, you’ll find a nice demo explorer that shows you what an API response will look like for a keyword and SERP location of your choice.
Figure 2 – DataForSEO SERP API demo explorer
DataForSEO SERP API response
Once again, you see that the API response is in a JSON-LD format that we mentioned in the FAQ section (see What is JSON-LD?). However, there is more to be seen in the demo explorer. We can directly pinpoint the JSON element of the response that we want to extract. Remember, for our test use case, we want to extract the different SERP item types.
Figure 3 – DataForSEO SERP API demo explorer with SERP items highlighted
Great, so we can let ChatGPT know that it can find the needed information in the JSON-LD of the API response under the key “item_types”. Plus, we could also add the demo response as an example in our prompt.
DataForSEO SERP API request
The next thing to figure out is what the API request should look like? For this, we can dive deeper into the API endpoint documentation that you can find here.
Figure 4 – DataForSEO SERP API documentation – Google Organic Live Advanced endpoint
As you can see in the sidebar, the SERP API offers different endpoint options. For the sake of this tutorial, we’re going with SERP API > Organic > Google > Live > Advanced.
The “Live” mode is a bit more expensive (0.002$ per keyword/SERP – see pricing info), but has the advantage of an instant response while other endpoints have a longer processing time. The “Advanced” endpoint gives us more detailed SERP information.
This documentation page also includes all the parameters that you can adjust for your API request (see below “Description of the fields for setting a task:”).
Some are required (e.g. keyword & location_code) while others are optional and use a default value if you don’t specify them (e.g. device with default ‘desktop’).
You can also find a list of all output fields that will be present in the response (see below “Description of the fields in the results array:”). There you’ll again find the field item_types and a pretty interesting overview of potential SERP items. On the documentation page, each SERP item gets explained in detail and includes a visual preview of what it looks like in the SERPs.
Figure 5 – DataForSEO SERP API documentation – SERP item types overview and preview
One final section to point out can be found on the right-hand side. Here, DataForSEO provides syntax examples in different coding languages, including examples of an API request and an API response. For Google Apps Script, let’s look at “NodeJS” because it includes JavaScript syntax as well.
Figure 6 – DataForSEO SERP API documentation – JavaScript example of API request
Below the API request (see “The above command returns JSON structured like this:”), it also includes an example of a response, again in the same JSON format as we already saw on the demo explorer page. These examples are ideal to include in our ChatGPT prompt to increase the chances that it gets the syntax right.
ChatGPT Prompt with input from API documentation
Now let’s put all the puzzle pieces together and get started with our first prompt.
To enrich our previous prompt, I simply added some info about the field item_types, the sample API request from the documentation and the beginning of the sample API response from the documentation. The beginning only, as the entire sample response would likely be too long for ChatGPT to process.
Since the prompts are also getting longer and longer, it’s best if you look at it directly on ChatGPT – because now we have the first prompt ready to go and can start scripting.
To be continued…
Good news, we’ve now done our homework and completed all the preparation to get started writing our script with ChatGPT. Along the way, we explored some basic knowledge about working with APIs, JSON-LD, Google Apps Script as well as DataForSEO and its SERP API documentation.
In the second part of this article, we will reap the benefits of our preparation. It will focus on getting the final script working by prompting ChatGPT and refining the output step by step.
