Diogo Abrantes da Silva

In this article, I’m going to talk about a Google Sheet I built that uses the WordPress REST API (or the WooCommerce API, if it’s an online store) to get all images from a website that don’t have an alt text associated with it and then uses OpenAI Gen AI to analyze the images and give them a description. After the sheet gives you the code to update all the alt text for the images at once in your phpMyAdmin console (I’ll explain why I used this method).

 

⚠️ Before you begin, please review the “What you need to know about this method” section. If you require assistance with the process, I am willing to help, provided you make a donation any of the NGOs I work with here.

There are WordPress plugins that do this without all the trouble of having a Google Sheet in the middle and using phpMyAdmin. You might want to consider using those.

 

The Google sheet

Click here to copy the Google Sheet

 

Why I Prefer This Method

1. I have more control over the descriptions I create, instead of relying on a plugin to do it without my knowledge.
2. OpenAI is not perfect, so I can review what it generates.
3. I can use it in a WooCommerce context, like the product name, to get a more optimized Alt Text from GPT-4.
4. Since it’s a Google Sheet, I can also use a Screaming Frog crawl and add more context in the prompt.
5. I usually work with NGOs, so this method is cheap and offers full control.

Bonus Features

1. The Google Sheet has a formula that flags when the AI can’t provide a description for an image.
2. You get a Google Sheet with AI formulas that you can use for other purposes (e.g., =IMAGEGPT and =GENGPT).

 

Instructions

Here’s how it works:

1. Getting the APIs

1. If you have a WooCommerce Store, use the WooCommerce API, which can provide more context about the image. You’ll need both the Consumer Key and Consumer Secret. To get the API, refer to the instructions in the Google Sheet.
2. If you don’t have WooCommerce, get the WordPress REST API key from your profile. The instructions are in the Google Sheet as well.

2. Adding the APIs

Once you have the APIs:

• Add them to the APIKey sheet in the Google Sheet, along with the URL of your domain (with https).
• If you have WooCommerce, add both the WooCommerce and WordPress APIs; otherwise, add only the WordPress API.

3. Adapting the Prompt

Modify the prompt in the Prompt sheet to suit your preferred language and needs:

• Choose the language for the alt text accordingly.
• Keep in mind that the more detailed the instructions in the prompt, the higher the cost will be.
• If you have WooCommerce, customize the prompt to use the product title for better optimization of the alt text.

4. Fetching the Images

Go to the Data sheet and click the “Get Images” option in your Google Sheet Menu. This will fetch all images without alt text from your website.

• If it’s your first time running the script, you’ll need to grant permission.
• If you have WooCommerce, your data will appear in a new sheet.

5. Generating Alt Text

Use the formula =IMAGEGPT(Prompt!$A$2,B2) to get OpenAI to analyze your image and generate an alt text description.

• Drag the formula in column C to apply it to all images.
• Try to do 100 images at a time to avoid overloading the OpenAI model.
• If you have WooCommerce, copy the formulas from the Data sheet to your new sheet.

6. Conditional Formatting

Use the conditional formatting feature to flag any potential issues in the descriptions. The current formula flags cells containing phrases like “I can’t,” “unable,” or “I don’t.”

Here’s the current formula:

=REGEXMATCH(C1:C263, "(unable|I can|I don)")

7. Updating Your Database

Finally, go to your server’s phpMyAdmin database (or use a plugin to access it) and paste the rules created in column D.

• Select the wp_postmeta table.
• Select the SQL option and click “Go.”