Automate Like a Pro with App Scripts & ChatGPT | Dani Leitner

This Post has been originally published on 23th of January 2025 on the Ranksuite Blog: https://ranksuite.io/blog/automate-like-a-pro-with-app-scripts-chatgpt/

Nearly everyone is using AI in one form or another these days. Whether it’s for ChatGPT chatboxes, writing content or finding recipes using the ingredients in your fridge. But the real power of ChatGPT lies elsewhere: in the things we can now do ourselves without spending hours watching tutorials or taking courses.

Take programming, for example.

Our daily work in SEO is full of repetitive tasks: copying data, cleaning up lists, creating reports – small tasks that don’t seem like much individually but end up consuming hours of our time. And let’s be honest: nobody really enjoys that.

But what if you could automate these tasks? Just like that. No programming course, no endless Googling.

That’s exactly what I’ve done – with Google Sheets and ChatGPT. In this article, I’ll show you how to become a programmer yourself (yes, really) and automate workflows with just a few clicks.

ChatGPT, Google Sheets & App Scripts: The Ultimate Trio for Automation

Everyone knows ChatGPT and Google Sheets. But have you heard of App Scripts?

App Scripts are essentially a lightweight development environment built right into Google Drive. They allow you to create plugins that perform specific actions in your documents – all without external tools or complicated installations.

The best part? You work directly within a Google Sheet (or Google Doc). All you need to do is open your document, click on Extensions in the top menu, and select App Script. Just like that, you’re in the development environment.

With App Scripts, you can do more than just edit content directly in your Google Sheet or Doc – like auto-filling cells, replacing text, adjusting layouts, or adding entire sheets. You can also interact between different documents or folders in your Google Drive.

For instance, you could copy data from one document to another or move files into specific folders. Additionally, you can connect external APIs to pull data directly into your Google Sheet.

Why App Scripts Are Better Than Formulas

Let’s talk about the benefits of App Scripts – with a simple example.

You’ve probably used the Google Sheets formula that fetches the H1, Title Tag, or Meta Description from a URL. It’s often the first step when performing a snippet audit (unless you’re pulling the data from Screaming Frog). Here’s the formula:

=IMPORTXML(A1, “//title”)

This formula works, no doubt. Then why go through the effort of writing an App Script instead?

Here are the biggest advantages of App Scripts:

Google Sheets can become painfully slow when handling a lot of formulas. App Scripts bypass this problem since they don’t run continuously.
Formulas reload every time you open the document. With App Scripts, you decide when the code runs.
When new snippets go live, also the content that the formula catches when you open up the Google Sheet changes. In contrast, an App Script updates only when you rerun it, giving you more control over the process.

In short: App Scripts are faster, more efficient, and won’t slow down your Google Sheet. Plus, they offer far more functionality than traditional formulas.

What If You Use Excel Instead?

If you prefer Excel over Google Sheets, you can also automate tasks – using macros.

Macros are small programs that automate repetitive tasks in Excel. They use VBA (Visual Basic for Applications), which is built into Excel.

You need to enable them, because they are normally hidden.

Here is how you do that:

Open Excel options.
Select Customize Ribbon and enable the Developer Tools.
You’ll now find the option to create macros in the ribbon.

Differences from Google Apps Script:

Programming Language: Macros use VBA, while Apps Script is based on JavaScript.
Environment: Macros are stored locally in Excel, while Apps Script operates in the cloud.
Flexibility: Apps Script allows for API integrations and working across Google Drive, whereas macros are limited to Excel files.

For more complex tasks, like moving files, you’d need an additional Python script for Excel. But even that can be easily generated with the help of ChatGPT.

And as always: if you’re unsure how something works, ask ChatGPT. It’ll guide you step by step – whether it’s for Google Sheets, Excel, or other tools.

Automation Use Cases: Practical Examples for SEO

When it comes to using ChatGPT for your specific challenges, one thing is key: creativity. The only real limit is your imagination – or rather, the ideas you come up with for what to automate.

For the use cases I’m about to share, keep one thing in mind: stay open! It’s not about copying these automations exactly as they are. After all, we all work a little differently. Instead, I want to inspire you to automate your own tasks: Which repetitive tasks are eating up your time? Which of those could you automate?

This is where the true power of a Large Language Model (LLM) like ChatGPT comes into play. It doesn’t just help you create automations; it tailors them precisely to your needs.

So, my goal isn’t to hand you a one-size-fits-all solution for your problem. Instead, I want to spark your creativity so you can find your own ways to optimise your workflows. Because that’s where the magic happens: turning tasks you genuinely hate into things that get done in seconds.

Use Case 1: Copying Templates to Client Folders

I organise my entire SEO workflow in Google Drive. Each client has their own folder with a predefined structure. For recurring SEO tasks, I’ve developed templates that I copy and customise for each client.

The traditional process looks like this:

Right-click on the template.
Select “Make a copy” and wait for the copy to appear in the same folder.
Right-click again to move the file to the correct client folder.
Open the folder and rename the document (adding the client name and date).
Finally, start the actual work.

Doesn’t sound like much work, does it? Maybe 5 minutes per document. But when you consider how many documents I copy for each client – from proposals to meeting notes to SEO Audits – those 5 minutes quickly add up to hours.

And let’s be honest: it’s just plain annoying.

One day, when I was supposed to be doing something else (you know the feeling), I thought: What if I could do all of this with a single click?

The Solution with ChatGPT & App Script

Step 1: Explain the Problem to ChatGPT

My prompt:

I organise my SEO workflow in Google Drive. Each client has a dedicated folder with a set structure. I’ve developed templates for recurring SEO tasks, which I copy and customise for each client.

I want to create a Google Sheet where I can, with the press of a button, copy the appropriate template into the client’s folder and rename it with the client’s domain and the current date. How can I implement this?

ChatGPT response:

To achieve your goal, you can follow these steps:

Prepare the Folder Structure:

Ensure each client folder in Google Drive has a standardised naming convention or unique ID. Store all templates in a folder named “Templates.”

Set Up a Google Sheet: Create a table with the following columns:
- Domain: The client’s domain name.
- Client Folder ID: The specific folder ID for the client in Google Drive.
- Template Type: The template to copy (e.g., “Audit”).
- Date: Optional, to include in the file name.
- Generated File Link: Where the script will insert the link to the copied file.

Implement Dropdowns: Add dropdowns for selecting the template type and validate data inputs.
Develop an Apps Script: Write a script that:
- Locates the template based on the selected type.
- Copies it into the client folder.
- Renames it with the domain and date.
- Inserts the file link into the Google Sheet.
Button hinzufügen: Insert a button in the sheet to run the script.
Error Handling: Add checks to ensure all required data is present before execution. Log any errors for troubleshooting.

Step 2: Prepare the Google Sheet

The next step is to prepare the Google Sheet with a sample client.

My prompt:

The folder structure is always the same for my client folders and already prepared. All templates are stored in a folder named “Templates.” How should the table in the Google Sheet be set up?

ChatGPT response:

Since the folder structure for your clients is always consistent, and the templates are centrally stored in a folder named “Templates,” the table in the Google Sheet can be simple and efficient. Here’s my suggestion for the structure:

Table Structure:

Client/Domain	Client Folder ID	Template Type	Date	Generated File Link
beispiel.de	1aBcD12345EFghIJK	Audit	2025-01-07	Link to the file

Table Structure:

Client/Domain	Client Folder ID	Template Type	Date	Generated File Link
beispiel.de	1aBcD12345EFghIJK	Audit	2025-01-07	Link to the file

Column Explanations:

Client/Domain:
A user-friendly name for the client or the domain (e.g., beispiel.de) to maintain clarity.
Client Folder ID:
The unique ID of the client folder in Google Drive (taken from the folder’s URL).
Example: In the URL https://drive.google.com/drive/folders/1aBcD12345EFghIJK, the ID is 1aBcD12345EFghIJK.
This allows the script to directly access the correct folder.
Template Type:
The name of the template to be copied (e.g., Audit, Keyword Research, etc.). These names must match the file names or IDs of the templates in the “Templates” folder.
Date:
The desired date to be included in the file name (optional). Example: 2025-01-07.
Generated File Link:
A column where the script will automatically insert the link to the new file after copying the template.

Step 3: Create the App Script

My prompt:

I’ve set everything up as you suggested. The templates always start with the name [Template] Content Audit. Can you program the App Script for me?

ChatGPT response:

Here is the Google Apps Script that meets your requirements.
This script will:

Locate the selected template in the “Templates” folder.
Copy it to the specified client folder.
Adjust the template name by adding the domain and date.
Write the link to the newly created file into the table.

Steps to implement the script:

Apps Script Code

Open your Google Sheet.
Go to Extensions > Apps Script and insert the following code:

function copyTemplate() {  // Spreadsheet und aktives Sheet  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();   // Hol die Daten aus der Tabelle  const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 5).getValues();
  // ID des Templates-Ordners  const templateFolderId = "TEMPLATES_ORDNER_ID"; // Ersetze mit der tatsächlichen ID deines "Templates"-Ordners  const templateFolder = DriveApp.getFolderById(templateFolderId);
  data.forEach((row, index) => {    const [domain, customerFolderId, templateType, date, createdLink] = row;
    if (!domain || !customerFolderId || !templateType || createdLink) {      // Skippe leere oder bereits verarbeitete Zeilen      return;    }
    try {      // Suche nach dem gewünschten Template      const files = templateFolder.getFilesByName(`[Template] ${templateType}`);      if (!files.hasNext()) {        throw new Error(`Template [Template] ${templateType} nicht gefunden.`);      }      const templateFile = files.next();
      // Kundenordner abrufen      const customerFolder = DriveApp.getFolderById(customerFolderId);
      // Template kopieren und Namen anpassen      const newFileName = `${templateType} - ${domain} - ${date}`;      const copiedFile = templateFile.makeCopy(newFileName, customerFolder);
      // Link zur neuen Datei in die Tabelle schreiben      const newFileLink = copiedFile.getUrl();      sheet.getRange(index + 2, 5).setValue(newFileLink);    } catch (error) {      Logger.log(`Fehler in Zeile ${index + 2}: ${error.message}`);      sheet.getRange(index + 2, 5).setValue(`Fehler: ${error.message}`);    }  });}

Settings and Adjustments

TEMPLATES_ORDNER_ID:
Replace TEMPLATES_ORDNER_ID with the actual ID of your “Templates” folder (e.g., 1aBcD12345EFghIJK).
Create a Button:
- In your Google Sheet, go to Insert > Drawing to insert a drawing or an image.
- Right-click on the drawing and select Assign script.
- Enter copyTemplate as the script name.
Test Run:
Fill in a row in your Google Sheet and press the button. The file should be copied, renamed, and the link inserted into the table.

Error Handling

If an error occurs, an appropriate error message will be displayed in the “Generated File Link” column.
Check the logs in Apps Script > View > Logs if the script unexpectedly terminates.

Step 4: Testing

Now it’s time to test what ChatGPT has guided you to set up. It’s perfectly normal if not everything works as expected right away.

The best thing you can do in such cases is simple: ask ChatGPT.
It will happily help you debug the code, fix errors, or identify potential issues.

Don’t hesitate to experiment and refine the setup – that’s part of the process!

Further Ideas for Automating Structures in Google Drive

Google Drive offers far more possibilities than just moving templates from one place to another. Here are some additional ideas I’m working on to make my SEO processes more efficient:

Automatically Creating Proposals from a Template: Instead of manually customising each proposal, a script could fill out specific fields automatically – like the client’s name, deliverables, address, website, and other relevant details.
Automating On-Page and Technical Audit Documents: A typical workflow often begins with a checklist, from which text blocks are manually transferred into a client-friendly document. An Apps Script could automate this process by compiling the selected points from the checklist directly into a ready-to-send audit document.
Creating Folder Structures for New Clients Automatically: When onboarding a new client, there are many repetitive steps, including setting up folders and subfolders. An Apps Script could generate this structure automatically as soon as a proposal is accepted.

Use Case 2: Extracting Breadcrumbs from a Website

Websites often contain a lot of useful data that we can use for analysis – we just need to know how to extract it. While there are several methods to accomplish this, personalised tasks like these are where ChatGPT truly shines.

I needed to extract the breadcrumbs from an online shop for a client. The goal was to quickly determine the depth of the category pages. Unfortunately, this couldn’t be seen from the URL structure alone. However, the breadcrumbs displayed exactly the information I needed.

The Solution with ChatGPT & App Script

Note: In this example, I’ve included the entire process for you. That’s because things don’t always work perfectly the first time we ask ChatGPT for something. This should also show you how to identify and fix errors with its help.

Step 1: Explain the Problem to ChatGPT

My prompt:

I have breadcrumbs at the top of this page <link to client’s category page> that I’d like to import into a Google Sheet to determine the category page’s level.

ChatGPT response:

To extract the breadcrumbs from the page and determine the category depth, you can follow these steps:

1. Crawl the Website or Specific URLs

Use a tool like Screaming Frog, Sitebulb, or a web scraping tool.
Make sure you can extract the breadcrumb data either into a specific field or directly from the HTML.

2. Automatically Extract Breadcrumbs with Apps Script

Here’s an example script using Google Apps Script to extract the breadcrumbs from a website and insert them into a Google Sheet:

Steps:

Open a new Google Sheet.
Go to Extensions > Apps Script and insert the following script:

function extractBreadcrumbs() {  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  const urls = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues();
  sheet.getRange(1, 2).setValue("Breadcrumbs");  sheet.getRange(1, 3).setValue("Category Depth");
  urls.forEach((url, index) => {    if (url[0]) {      const response = UrlFetchApp.fetch(url[0]);      const html = response.getContentText();      const breadcrumbs = html.match(/<nav[^>]*class=".*breadcrumb.*">.*?<\/nav>/s);
      if (breadcrumbs) {        const breadcrumbText = breadcrumbs[0].replace(/<[^>]+>/g, ' > ').trim();        const depth = breadcrumbText.split('>').filter(Boolean).length - 1;               sheet.getRange(index + 1, 2).setValue(breadcrumbText);        sheet.getRange(index + 1, 3).setValue(depth);      } else {        sheet.getRange(index + 1, 2).setValue("No Breadcrumbs Found");        sheet.getRange(index + 1, 3).setValue(0);      }    }  });

Using the Script:

Enter the URL(s) into the first column of the sheet.
Run the script: Go to Apps Script > play icon.
The breadcrumbs and their depth will appear in columns B and C.

3. Result Analysis

The breadcrumbs will be displayed in the second column, and the category depth in the third.
The depth is calculated based on the number of elements separated by > (minus 1, as the homepage is often counted as the root).

Step 2: Refining the Result

In this case, I had to follow up a bit to ensure the script worked right.

My prompt:

The first row is a header. Is that accounted for?

ChatGPT Response:

Good point! The script doesn’t account for that. To ensure the first row with headers is ignored, the script needs to be adjusted. Here’s the corrected version:

function extractBreadcrumbs() {  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  const urls = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues(); // Start bei Zeile 2, ignoriert die erste Zeile
  // Überschriften für die Ausgabe  sheet.getRange(1, 2).setValue("Breadcrumbs");  sheet.getRange(1, 3).setValue("Category Depth");
  urls.forEach((url, index) => {    if (url[0]) {      try {        const response = UrlFetchApp.fetch(url[0]);        const html = response.getContentText();        const breadcrumbs = html.match(/<nav[^>]*class=".*breadcrumb.*">.*?<\/nav>/s);
        if (breadcrumbs) {          const breadcrumbText = breadcrumbs[0].replace(/<[^>]+>/g, ' > ').trim();          const depth = breadcrumbText.split('>').filter(Boolean).length - 1;                   sheet.getRange(index + 2, 2).setValue(breadcrumbText); // Zeile index + 2, um Überschrift zu berücksichtigen          sheet.getRange(index + 2, 3).setValue(depth);         // Zeile index + 2        } else {          sheet.getRange(index + 2, 2).setValue("No Breadcrumbs Found");          sheet.getRange(index + 2, 3).setValue(0);        }      } catch (e) {        sheet.getRange(index + 2, 2).setValue("Error fetching URL");        sheet.getRange(index + 2, 3).setValue("-");      }    }  });}

Changes:

URL Range: The range for the URLs now starts from the second row (getRange(2, 1, sheet.getLastRow() – 1, 1)), ignoring the first row.
Output Position: Results are inserted starting from the second row (index + 2).

Application:

The header remains in the first row.
URLs should be entered starting in row 2, column A.
Results (Breadcrumbs and Depth) are output in columns B and C, starting from row 2.

Step 3: Prepare the Google Sheet & Set Up the App Script

As before, the next step is to prepare the Google Sheet and copy the code into the App Script editor.

Start by adding the headers “URL” in column A, “Breadcrumbs” in column B, and “Category Depth” in column C. Enter the URLs to analyse starting in row 2 of column A.

Next, open the Apps Script Editor via Extensions > Apps Script, paste the refined script, and save it. Once done, run the script to see if it works correctly.

Step 4: Fixing Errors

In my case, a problem occurred. The script returned “no breadcrumbs found” in every row, which couldn’t be correct. My first guess was that ChatGPT hadn’t actually analysed the URL I provided (which can happen sometimes). I decided to point this out and give it a second try.

My prompt:

An error occurs: “no breadcrumbs found.” Can you take a closer look at this website and search for the breadcrumbs in the code? <Link to category page>

ChatGPT response:

I’ve examined the website <Link to category page> and found that the breadcrumb navigation is not located in a <nav> element with the class “breadcrumb,” as initially assumed. Instead, it is contained within a <div> element with the class “breadcrumb.”

To extract the breadcrumbs correctly, you’ll need to update the script. Here’s the revised version:

function extractBreadcrumbsOptimized() {  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  const urls = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues(); // URLs ab Zeile 2  const results = [["Breadcrumbs", "Category Depth"]]; // Überschriften für die Spalten B und C
  urls.forEach((url) => {    if (url[0]) {      try {        const response = UrlFetchApp.fetch(url[0]);        const html = response.getContentText();        const breadcrumbs = html.match(/<div[^>]*class="[^"]*breadcrumb[^"]*">.*?<\/div>/s);
        if (breadcrumbs) {          const breadcrumbText = breadcrumbs[0]            .replace(/<[^>]+>/g, ' > ')            .replace(/\s+>/g, '>')            .replace(/>\s+/g, '>')            .replace(/>{2,}/g, '>')            .replace(/(^\s*>\s*|\s*>\s*$)/g, '')            .trim();          const depth = breadcrumbText.split('>').filter(Boolean).length - 1;          results.push([breadcrumbText, depth]);        } else {          results.push(["No Breadcrumbs Found", 0]);        }      } catch (e) {        results.push(["Error fetching URL", "-"]);      }    } else {      results.push(["No URL", "-"]);    }  });
  // Schreibe die Ergebnisse in einem Schritt ins Sheet  sheet.getRange(1, 2, results.length, 2).setValues(results);}

Changes to the Script:

Regular Expression Update:
The regular expression was modified to search for a <div> element with the class “breadcrumb”:

const breadcrumbs = html.match(/<div[^>]*class="[^"]*breadcrumb[^"]*">.*?<\/div>/s);

Cleanup of Extracted Text:
After removing HTML tags, additional spaces and unwanted > characters are cleaned up to ensure the breadcrumb format is correct:

const breadcrumbText = breadcrumbs[0]

            .replace(/<[^>]+>/g, ' > ')

            .replace(/\s+>/g, '>')

            .replace(/>\s+/g, '>')

            .replace(/>{2,}/g, '>')

            .replace(/(^\s*>\s*|\s*>\s*$)/g, '')

            .trim();

Note: The structure and class names of websites can vary. It’s important to carefully analyze the HTML of each page and adjust the script accordingly to correctly extract the desired elements.

Recommendation:
Check the HTML code of your target pages to confirm that the breadcrumbs are within a <div> element with the class “breadcrumb.” If the structure is different, modify the regular expression in the script as needed.

If you need further adjustments or assistance, feel free to reach out!

Step 5: Testing

After the error was fixed and ChatGPT properly analysed the website, everything now works and the breadcrumbs are extracted.

Further Ideas for Extracting Data

Apps Script offers countless possibilities for extracting data directly from websites and seamlessly integrating it into your workflow. Here are a few more examples of how I use it:

Retrieve Current Snippets: Extract title tags and meta descriptions to quickly analyse them or incorporate them into a snippet audit.
Extract Authors and Publication Dates from Blog Posts: Useful for content audits or organising an editorial calendar after publishing a post.

API Integration in App Script

Another great thing we can do with App Script is to integrate APIs. With APIs, you can interact with external tools, retrieve data, or automate tasks that would otherwise take a lot of manual effort.

What is an API?

APIs (Application Programming Interfaces) allow you to access the features and data of tools through code.
You can think of them as a bridge between your Google Sheet and external services. For this bridge to work, you usually need an API key, which the tool provides.
Not every tool offers an API, but many of the big providers do. The best part? ChatGPT can make accessing and using an API incredibly simple.

Important Tips for Working with APIs

When working with APIs, there are two things to keep in mind to ensure secure and efficient usage.

Never Share Your API Key

The API key is like your personal access code. With it, others could not only retrieve your data from the tool but sometimes also modify content or trigger actions – which, in some cases, could even cost money.

It’s crucial to never share your API key. If you need help from ChatGPT, remove the API key from your code and replace it with a placeholder like API_KEY_HERE before sharing. This keeps you protected and prevents unauthorised access to your data.

API Documentation

To use an API effectively, you need to understand how it works and what commands or functions are available. For this, APIs come with documentation that explains everything a developer needs to know to use the interface. Since our “developer” here is ChatGPT, you need to share this information with it.
The best approach is to provide ChatGPT with the link to the API documentation or paste the relevant section directly into your prompt. As we’ve seen before, ChatGPT sometimes is lazy and ignores links.

Examples of API Documentation

SE Ranking API Documentation:This API gives you access to keyword data, rankings, and other SEO-relevant metrics. For instance, you can update search volumes directly in your Keyword Research.
Bexio API Documentation: Bexio is an invoicing and accounting tool. With this API, you can for example automate the import of expenses into your account.
Google APIs Explorer: Google offers a wide range of APIs for various applications, from Analytics to Calendar integrations.
DataForSEO: This is even a service fully dedicated to SEO data via APIs.

Ideas for API Integrations in Google Sheets

The possibilities are nearly endless. Here are a few specific use cases I’ve implemented or am currently working on:

Importing Bexio Expenses:

When I switched from Google Sheets to Bexio for my accounting, I had to import a large amount of data. Using the Bexio API and an App Script I created with ChatGPT, I was able to efficiently integrate all expenses from an entire year – saving countless hours of work.

Updating Search Volume in Content Audits:

In content audits or keyword research, it’s often important to update search volumes, as months can pass between the initial research and the final implementation. With the SE Ranking API and App Script, this is no problem: I can update the data in seconds directly in my Google Sheet.

Take a look at the tools you use daily and check if they offer an API. If they do, think about which tasks you could automate with it.

It’s almost like being a real pro

I hope the examples in this post have sparked your creativity and inspiration. Sure, at first, it might feel like you’re putting in a lot of effort to automate something that only takes two minutes manually. But trust me, in the long run, you’ll be more than grateful for this one-time investment.

And let’s be honest, it feels pretty amazing when you’ve programmed a little script that just works.

If you’ve already optimised processes yourself or have questions about it, feel free to share them in the comments. And if you want even more ideas like this, sign up for my newsletter. Alongside SEO tips, I occasionally share insights and tips like these.