The main goal of this article is to give you a practical walkthrough about how to write a Google Apps Script from scratch together with ChatGPT. Our example use case is to access the DataForSEO SERP API with a list of keywords, to extract the SERP item types for each keyword and to write them in a Google Sheet in our preferred format.
We already did the heavy lifting in part one of this article where we:
- Explored some basic knowledge about working with APIs, JSON-LD, Google Apps Script as well as DataForSEO and its API documentation;
- And crafted our first prompt with all the necessary input to get the script writing with ChatGPT started.
Take a look at the initial prompt to see where we left off.
Now, it’s time to put things into practice and actually start scripting with ChatGPT. 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 have documented the different stages of the Apps Script on GitHub:
- GitHub repository
- ReadME with different script versions and the final Apps Script
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 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.
Writing a Google Apps Script for API access with ChatGPT
The last step that we completed in part one of the article was to enrich our prompt by adding some information from the DataForSEO SERP API documentation. This includes the field item_types that has the information that we want to extract from the API response, the sample request from the documentation and the beginning of the sample response. Time to get started: ChatGPT, write us a script!
Apps Script Version 1
“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. They can be retrieved in the API response field named “item_types”.
This is some example request from the documentation in NodeJS:
(see in full prompt)
This is the beginning of the example response in the documentation:
(see in full prompt)”
Nice! We received a first script that we can test directly in our Apps Script coding environment. The sample sheet already contains the final script.
If you want to replicate and test things from scratch, you have two options:
- Overwrite the final script in “dataforseo-serp-api.gs”.
- Create a blank Google Sheet document, copy the two tabs “DataForSEO Settings” and “DataForSEO Output” to this new sheet and create a new script file there.
To run a first test, the only things left to do are to (1) add a DataForSEO username and password, (2) save the code, (3) add keywords and settings in the tab “DataForSEO Settings” and (4) press “Run” to see what happens.
Figure 1 – SERP API script – version 1 added to Google Apps Script editor
Now that we have run our first script, what happened? Quite a bit! Here is the input (see tab “DataForSEO Settings”) and output (see tab “DataForSEO Output”):
Figure 2 – Version 1 of SERP API script – input in Google Sheets tab “DataForSEO Settings”
Figure 3 – Version 1 of SERP API script – output in Google Sheets tab “DataForSEO Output”
Looks good already! We have a list of keywords and the respective SERP items for each. But, we can still fine-tune a few things. The “Search Device” column doesn’t seem correct yet. Also, in the columns “SERP Country” and “SERP Language” it would be nice to read the country/language names instead of the codes.
First things first, why does the “Search Device” column only show “-” for each keyword? A quick check of the code reveals the culprit; there was a mistake in the prompt instructions. We get the “device” value from cell C4 instead of the correct cell B4.
Figure 4 – Version 1 of SERP API script – mistaken cell value for device
Figure 5 – Version 1 of SERP API script – mistaken cell value for device
We can fix this quickly and manually in the code. Still, one question remains:
How did the script work without an error even though it received a wrong value for the device? Let’s use the Apps Script Debugger to find out!
Apps Script Debugger
The Apps Script Debugger allows you to set breakpoints in your code, pausing its execution so you can inspect variables and navigate through the different lines of code. It’s a handy tool for understanding and fixing issues in your script efficiently.
In our case, we want to have a look at the API response, so let’s set a breakpoint at line 52, right after we received the API response and converted it to readable JSON.
When we now run the script by clicking “Debug”, we can look inside the computer brain at this moment in time, i.e. when it reaches line 52.
Figure 6 – Version 1 of SERP API script – debugging wrong device information
Digging deeper into the API response shows something interesting: the script used the device ‘desktop’ instead of ‘mobile’ – why is that? In lines 32 to 36 we see the reason. It is because ChatGPT did not include the device in the payload (i.e. the input data) sent to the API. Without this instruction, the API chose the default value “desktop” instead.
Now, for script version 2, we have a few things on the to-do list for ChatGPT:
- Add the device in the payload.
- In the output written in the sheet, don’t use the country code (cell C2) and the language code (cell C3). Use the country name (cell B2) and language name (cell B3) instead.
- Also, when testing, I noticed that the script always clears the output sheet. But I just want it to append a new API output row and to not delete earlier results.
Instead of trying to change the code ourselves, we can ask ChatGPT to refactor it.
Important disclaimer: If you add existing scripts, NEVER paste your API credentials or passwords into ChatGPT. If it happens by accident, make sure to reset them.
Apps Script Version 2
“Here is my current script:
[add your script WITHOUT credentials]
Refactor it with the following instructions:
The device information is currently not sent to the API in the request. Add the selected device in the API payload.
In the output written to the sheet, don’t use the country code (cell C2) and the language code (cell C3). Use the country name (cell B2) and language name (cell B3) instead.
Don’t clear the output sheet, just append new results. The header row will always be present already.”
Figure 7 – Version 2 of SERP API script – output in Google Sheets tab “DataForSEO Output”
Nice! That worked like a charm now. Let’s double-check in the code if the API actually received the right device. Instead of using the debugger, this time we’ll use another handy piece of JavaScript syntax for debugging: console.log()
You can use this technique anywhere in the script to visualize a value at this point in the script’s control flow (more info on control flow in part one). In our case, we want to use it to visualize the API response by logging it to the Execution log.
Figure 8 – Version 2 of SERP API script – debugging wrong device information
Side note: why did we use console.log(data.tasks[0]["data"]); ? This way, we logged only the specific nested part of the API response with the information relevant to us. Have a look at part one of the article – “What is JSON-LD?” for some more info on this.
The new code seems to work fine! After pushing it to GitHub, I was able to double-check the differences compared to the previous version. You can also use a tool like DiffNow to compare script differences. Now I have noticed that the script does not use the language/country codes at all, just the names.
Figure 9 – GitHub Differences between Version 1 & Version 2 of SERP API script
The API still works with sending language/country names as input, but I prefer the codes. Another point for the ChatGPT to-do list.
Besides this, there is one last feature that I want to add to the script: it should check if a keyword from the input list is already in the output list before it sends it to the API.
I added a formula in the tab “DataForSEO Settings” in column B that checks a box, if the keyword is in the output list. Now, the script simply needs to check if this box is ticked and only process the keywords where it is not, e.g. “berliner luft” in the screenshot.
Figure 10 – new feature request in “DataForSEO Settings” for Version 3 of SERP API script
Apps Script Version 3
Let’s put the latest instructions in a prompt:
“Here is my current script:
[add your script WITHOUT credentials]
Refactor it following these instructions:
Only use the country name (cell B2) and the language name (cell B3) for the data written to the output sheet “DataForSEO Output”.
Still use the country code (cell C2) and the language code (cell C3) in the payload for the API.
Also, I added a column with checkboxes next to the keyword list. The checkboxes are in column B in the sheet “DataForSEO Settings” and they are checked if the keyword is already included in the output list.
Now the script should only process the keywords where the box is not checked.”
Again, we add the script in our editor and the credentials and run it. When running the script, it quickly becomes clear that for some reason, ChatGPT removed the row that grabs the device value var device = settingsSheet.getRange("B4").getValue(); which resulted in this error:
Figure 11 – Version 3 of SERP API script – error due to missing device information
I simply pasted this error into ChatGPT, and it refactored the script as needed to version 4.
Apps Script Version 4
However, when testing the script once more, it still did not work yet. Instead of only processing the keyword “berliner luft”, it processed all keywords, including the empty rows.
Up until this point, I was on ChatGPT autopilot without really going through the script myself. Now, it was necessary to take a closer look because I was curious about the value of the newly introduced “checkbox” variable that decides if a keyword gets processed.
If it is TRUE, the keyword should be skipped, if it is FALSE, it should be sent to the SERP API. Looking at the script and logs, it seems like ChatGPT did not just grab the value TRUE/FALSE from the checkbox cell, but it mistakenly used the formula itself.
Figure 12 – Version 4 of SERP API script – debugging wrong checkbox information
This explained the weird behavior of the script. With some knowledge of the JavaScript syntax and debugging, it was easy to spot that something was not right. But that doesn’t mean that we actually need to write code to now fix it. Time to tell ChatGPT that we need another redo, using clearer instructions than before.
Apps Script Version 5
“This does not work correctly yet. I only want to process keywords where the checkbox in column B of my Google Sheet is not checked (i.e. it is false). I only want to process rows where a keyword is present. The current script also processed empty rows.”
Right after the prompt, I noticed in the script answer that it updates column B itself, by setting it to TRUE and overwriting the formula. So, I added some more context and clear instructions not to overwrite the formula.
“The script does not need to update the value in column B. This happens automatically via a formula if the keyword is in the output list.”
Finally, only the row “berliner luft” was now added in the output, just as we wanted. It also still wrote “United Kingdom” and “English” instead of the codes and a look at the payload in the scripts confirmed that here the codes are used as briefed.
Last but not least, let’s make running the script a bit more convenient by adding a custom menu. This allows us to run the script directly from the Google Sheets UI.
Apps Script Version 6
“Create a script for a custom menu so that I can start the script from the Google Sheets UI.”
I only added the menu part on top of the script, which worked as expected.
Figure 13 – Version 6 of SERP API script – custom menu code for starting the script
Sidenote: In the sample sheet, I commented this part out in the code file “dataforseo-serp-api.gs” and added it to the file “custom-menu.gs” instead. Creating custom menus in two code files interferes with each other.
Figure 14 – Version 6 of SERP API script – custom menu button for starting the script
One last disclaimer before we wrap up: you won’t be able to process endless keyword lists because Google Apps Scripts timeout after running for six minutes. There are advanced techniques to circumvent this, but they go beyond the scope of this article.
Another approach to speed things up and to process data more efficiently is to use the standard Task POST endpoint of the SERP API. Here you can send keyword data in batches but don’t get an immediate response. Still, you can check with the Tasks Ready endpoint if your data has finished processing and then use the Tasks GET endpoint to download it.
Wrapping up
Time to call it a day. What our final example script can do might seem simple, but if you look back, we learned quite a lot while creating it:
- We’ve explored how to use Apps Script to access APIs.
- We’ve decoded API documentation to understand what values we want to extract from the API response.
- We prompted ChatGPT to write SEO automation scripts and included valuable examples from the API documentation.
- We debugged and updated our prompts with instructions for ChatGPT to refactor our script until it meets our criteria.
Along the way, we introduced many concepts that will help you with crafting prompts for ChatGPT to automate SEO with Google Apps Script in the future.
Simply pulling SERP item data for a keyword list might be covered by existing SEO tool exports, but you now have the skills to address your own individual use cases:
- Is there any information in the SERPs that you can use strategically?
- Are there other DataForSEO APIs that you might want to leverage?
- Do you have access to other tool APIs that could help to speed up your workflows?
And last but not least, there are two more things that I want to leave you with:
Knowing the basic JavaScript syntax can really help you to work more efficiently with AI. ChatGPT will likely give you working scripts – until it doesn’t. And once you know the basic flow of a script and where to look, you can fine-tune prompts better.
Secondly, if you’re interested in coding, Apps Script might even be a nice bridge into the world of programming. At least that was my experience: knowing some SEO and how to work with Google Sheets formulas helped me a lot in learning the JavaScript syntax. And it also got me hooked on wanting to learn more.
