New AI apps are exciting…

But, using AI within familiar tools is powerful and easier to adopt.

In this post, I share how I used ChatGPT and Perplexity within Google Sheets (Excel) to migrate my prompts library to notion.

Specifically, I use AI for discrete practical tasks:

• data extraction

• web scraping

• summarization

• applying content tags

(my Labor Day Weekend project 😁)

Here’s the Youtube version of this post:

Table of Contents

• GPT For Work

• Scrape Prompt URLs

• Extract Name

• Summarize Web Page

• Safe Mode

• Conclusion

GPT For Work

First, let’s setup the extension, GPT For Work.

This extension provides built-in AI-powered functions for Excel and Google Sheets, including:

• GPT Extract: Extract specific information from text, like email addresses or company names.

• GPT Summarize: Create concise summaries of longer texts according to specified formats.

• GPT Classify: Classify text into predefined categories for easy organization.

• GPT Translate: Translate text between various languages with optional specifications for source language.

• GPT List / HList: Generate vertical or horizontal lists of responses based on prompts.

• GPT Split / HSPLIT: Split text semantically into sections, paragraphs, or sentences.

• GPT Fill: Automatically fill or cleanse a range of cells based on a few examples.

• GPT Table: Create structured tables from responses to prompts.

• GPT Match: Find matches between two columns based on similarity.

• GPT Format: Reformat data such as dates or currencies and fix capitalization issues.

• GPT Edit: Edit text for grammar, spelling, or style based on user-defined tasks.

• GPT Tag: Automatically suggest or apply relevant tags to text.

• GPT Web: Access and extract up-to-date information from the web based on queries.

• GPT Vision: Analyze images by generating descriptions or extracting information.

• GPT Create Prompt: Construct prompts from scattered cell data for enhanced prompt generation.

These built-in AI functions turbocharge data manipulation, cleaning, and analysis capabilities in Excel and Google Sheets.

View the full list here.

Here’s how to setup GPT For Work:

1. Visit gptforwork.com to install the extension compatible with Google Sheets/Docs or Microsoft Office (Excel, Word).

2. After installation, open Google Sheets.

3. Navigate to Extensions > GPT for Sheets and Docs > Open.

4. You should see the extension open.

5. Go to Settings > API Keys > configure your OpenAI API key.

Scrape Prompt URLs

Before diving into Excel/Sheets, let’s prepare our data.

I want to scrape all prompt URLs from my existing github repo.

Here’s a custom script that I use all the time.

It can scrape URLs from a specific webpage. I’ve used this script to scrape my own TikTok video URLs, Youtube video URLs, etc.

Let’s repurpose it to scrape my prompt URLs by updating the second command with this base URL: /SabrinaRamonov/prompts/blob/main/

// Here's the full script. Copy and paste it into your browser console log.

// Create array to store links
let links = []

// Navigate to user's page and scrape links
// Customize this based on what you're scraping
$x('//a[contains(@href, "/SabrinaRamonov/prompts/blob/main/")]').forEach(el => links.push(el.href))

// Convert the array to a string
const arrayString = JSON.stringify(links, null, 2);

// Create a Blob with the data
const blob = new Blob([arrayString], { type: 'text/plain' });

// Create a URL for the Blob
const url = URL.createObjectURL(blob);

// Create a temporary anchor element
const a = document.createElement('a');
a.href = url;
a.download = 'githubLinks.txt';

// Programmatically click the anchor to trigger the download
a.click();

// Clean up by revoking the Blob URL
URL.revokeObjectURL(url);

Open your browser console, paste this script, and hit Enter.

It will download the links to a TXT file, which I named githubLinks.txt

Import the TXT file into Sheets or Excel.

Let’s name the column Prompt URL.

Before using AI, I removes duplicates and unwanted links in the Google Sheet.

Go to Data > Data Cleanup > Remove Duplicates.

Now, create 3 more columns:

• Name

• Description

• Tags

AI will populate these columns for us…

Extract Name

Now, the fun part!

Use the built-in function GPT_EXTRACT to pull the names from each prompt's URL and format them neatly.

Here’s the documentation for this function.

Here’s the formula:

=GPT_EXTRACT(A2,"name of the markdown file, without the .md extension, capitalize the first letter of each word")

After applying this AI-powered formula to multiple rows, just like any other Excel/Sheets formula, here’s how our data looks:

Nice! 👍️ 

This data extraction task extracts the filename from the URL, removes the markdown extension md from the end of the filename, and capitalizes the first letter of each word.

I typically test the formula on a few rows to ensure accuracy before applying it to the entire dataset. This is what I’m used to doing in Google Sheets / Excel with any other formula!

Now we have a clean and organized list of prompt names.

Summarize Web Page

Next, I want AI to go to the prompt URL, read the prompt, and summarize it.

I use the built-in function GPT Web which uses Perplexity.ai under the hood.

As of Sept 2024, ChatGPT-4o does not allow web search via the API, sadly!

Here’s the documentation for this function.

And, here’s the function:

=GPT_WEB("Navigate to the provided URL, which contains a single ChatGPT prompt. Your task is to summarize the ChatGPT prompt in 2 sentences. Output only your final answer. Be direct and concise. For example, instead of starting your answer, 'The prompt is designed to analyze prose...', simply start your answer with 'Analyze prose...'. Here's the URL:", A2)

My goal is to get a clear, two-sentence description of each prompt.

By using this AI-powered function, I skip the step of scraping each prompt’s full contents.

I simply tell Perplexity to navigate to each URL and summarize.

Saves a lot of time and super efficient! 🍾 

Here’s how our updated data looks:

Apply Tags

Last, I use AI to apply tags to each prompt for better organization and categorization in my upgraded prompt library.

Tags make it easier to navigate the collection of prompts by allowing you to search for specific tags.

GPT For Work’s built-in GPT_TAG function offers 2 options:

• allow ChatGPT to generate automatic tag suggestions

• feed in a user-defined list of tags

Here’s the documentation for this function.

To obtain a user-defined list of tags, I simply ask ChatGPT to brainstorm them and output a CSV, which I import into my Sheets.

Here’s the prompt:

I have a list of 10,000 prompts. Based on typical usage of ChatGPT, brainstorm 100 tags for the prompts that cover all the most common use cases. Output your answer in a CSV. Each tag should be no more than 2 words long.

Here’s the formula:

=GPT_TAG(C2,Tags!A:A,,3)

I put my user-defined tags in a separate sheet, which I reference in the formula with Tags!A:A.

I test both approaches and compare the results:

• 3rd column contains ChatGPT generated tag suggestions

• 4th column contains tags from my user-defined list

I like the 4th column of tags much better!

Let’s go ahead with my user-defined list, applying the GPT_TAG formula to the remaining rows.

Here’s our final dataset, ready to import into Notion!

Safe Mode

GPT For Work has an option called Safe Mode, which helps reduce costs by preventing unnecessary recalculations when values change.

Especially useful after applying formulas in bulk!

I enable Safe Mode to reduce operational costs and prevent unnecessary recalculations caused by changes in cell values. It prevents the entire sheet from recalculating all formulas repeatedly.

For example:

If I change a single character in ONE of my tags…

the ENTIRE Tags column recalculates!

Even though I only updated ONE tag entry!

By enabling Safe Mode, I keep costs lower while ensuring the integrity of the data. The trade-off is speed.

Operations run more slowly, but the savings (and sanity) are well worth it.

However, if I want to test changes quickly, I’ll turn Safe Mode off temporarily.

Here’s more information about how to avoid recalculations in GPT For Work.

Conclusion

I truly enjoy using AI capabilities embedded in apps and tools I already love.

But, I hate GPT For Work’s pricing. That’s the only thing 😅 

Altogether, integrating ChatGPT and Perplexity in Google Sheets / Excel simplifies data cleaning and processing. You can instantly apply prompt transformations to bulk data — imagine hundreds of use cases for this!

In this post, I walked through a practical example migrating my prompts library from Github to Sheets (which I can import directly into notion).

If you want to see more examples of AI in everyday tools like Excel and Google Sheets, reply to this email and let me know what you’re looking for!

Did I miss anything?

Have ideas or suggestions?

Message me on LinkedIn 👋

Sabrina Ramonov

P.S. If you’re enjoying my free newsletter, it’d mean the world to me if you share it with others. My newsletter just launched, every single referral helps. Thank you!

• share by copying and pasting the link: https://www.sabrina.dev