-
Contents
Basic Usage
Install
Install the SEO Toolkit for Sheets™ add-on from the Google Workspace™ Marketplace.
Launch
Once installed, select Extensions > SEO Toolkit for Sheets > Open Sidebar from any Google Sheet.
Screens
The SEO Toolkit for Sheets™ add-on is split into three screens or tabs:
Functions: The functions screen list all available functions.
Recipes: The recipes screen stores saved functions and also contains an import feature.
Settings: The settings screen contains options for function visibility and URL cache duration.
Functions
Click on a function accordion to expand it.
All Functions
The following options are available for all functions.
Send to Range
Each function provides sheet options, highlighted in green. Choose Send to Range to send results to a specific range, normally within the same sheet.
URL Range: The column containing the list of URLs. Use the ⊞ icon to select the currently highlighted range.
Output Range: The range to send results to. Must have the same number of rows as the URL range.
Include Header Row: Whether the output range should include a header row. If selected, the URL range should also include a header row.
Send to Sheet
Choose Send to Sheet to send results to an existing sheet within the current spreadsheet.
URL Range: The column containing the list of URLs. Use the ⊞ icon to select the currently highlighted range.
Select Sheet: Choose an existing sheet to send results to. Use the 🗘 icon to refresh the list of sheets when new sheets are added, removed or renamed.
Append or Overwrite: Whether results should be appended to the target sheet or overwrite existing data.
Start at Cell: If Overwrite is chosen, which cell results should start from.
Clear Existing Data: If Overwrite is chosen, whether the sheet should be cleared first before results are written.
Prepend Lookup Column: Check to prepend a lookup column to results. This is useful to distinguish between duplicate rows when running multiple functions on the same URL list.
Lookup Range: The column containing the list of lookup values. Must have the same number of rows as the URL range.
Expand Rows: Some functions will combine multiple results into a single row by default. Enable this option to output each result on a separate row.
Include Header Row: Whether the output range should include a header row. If selected, the URL range should also include a header row.
Run and Save
Each function provides actions to run and/or save, highlighted in blue.
Save to Recipe: Check to save the function to a recipe for future use. Recipes are a collection of one or more functions that can be stored and shared.
Recipe: Choose an existing recipe to save the function to or type in the name for a new recipe.
Function Name: Give the saved function a name.
Run: Execute the function and optionally save the function if Save to Recipe is checked.
Save: Save the function without executing it.
Utilities
Utility functions are primarily used to fetch data directly from target URLs e.g. status codes, word counts or on-page elements.
Status Code
Gets the HTTP status code for a list of URLs.
Initial (Status Code): The first status code encountered e.g. 301.
Final (Status Code): The final status code encountered e.g. 200.
Chain (Status Code): The chain of status codes encountered e.g. 301 > 200.
Destination URL: The final URL or next URL when Initial Status Code is selected. Cannot be combined with Final Status Code.
Word Count
Gets word counts using CSS selectors for a list of URLs.
CSS Selectors: One or more CSS selectors targeting text areas on the page. Each comma separated selector will be output to a separate column. Use body to get the total word count for the page.
Fetch Element(s)
Fetches HTML element(s) using CSS selectors for a list of URLs.
CSS Selectors: One or more CSS selectors targeting specific elements on the page. Each comma separated selector will be output to a separate column. Follow each selector with a pipe | to specify a return type other than text, options include |html, |attr(name), |boolean or |text (default).
Return First Match Only: Returns the first match for the CSS selector. Uncheck to return multiple matches. Multiple matches are returned in the same cell separated by a line break.
Some common selectors for SEO are listed below.
Title Tag and Meta Description
title, meta[name=description]|attr(content)
Hreflang (Uncheck Return First Match Only)
link[hreflang]|attr(href),link[hreflang]|attr(hreflang)
Favicon URL
link[rel~=icon]|attr(href)
RegEx Extract
Extracts content using a RegEx pattern for a list of URLs.
RegEx Pattern: A regex pattern targeting specific text or code on the page. If required, use a regex tester set to ECMAScript (JavaScript) to experiment with regex patterns.
RegEx Flag: The regex flag or flags, defaults to global (g).
Capture Groups (Optional): The number of a specific capture group or groups to return. Leave blank to return the complete match.
Return First Match Only: Returns the first match for the regex pattern. Uncheck to return multiple matches. Multiple matches are returned in the same cell separated by a line break.
Some common patterns for SEO are listed below.
Google Tag Manager ID
GTM-[A-Z0-9]{6,7}
Redirect Rules
Creates a list of redirect rules to be imported into your chosen platform. The output can be downloaded as a CSV file via the File > Download menu.
Redirect from Range: The list of URLs to redirect from.
Redirect to Range: The list of corresponding URLs to redirect to.
Platform: Select the target platform to format redirects for.
Remove Domain Prefix (Optional): The root domain to be removed to make URLs relative, separate multiple domain prefixes with a pipe | as required.
Redirect Type: Whether redirects should be 301 (permanent) or 302 (temporary).
Historic URLs
Creates a list of historic URLs from the Wayback CDX.
Domain: The target domain.
Exclude Images: Filters out URLs with image file extensions.
Google Search Console
Google Search Console functions connect to the Search Console API to retrieve URL inspection and search analytics data.
Search Console Authentication
Google Search Console will be automatically authenticated for your primary Google account when you install and enable the SEO Toolkit for Sheets™ add-on.
To use a different Google account for Google Search Console access click Switch Account to launch the Google account selector in a pop-up window.
Note, you can revoke Search Console access by clicking "Revoke GSC access" at the bottom of any Search Console function.
This will also reset add-on permissions and close the sidebar. Any saved recipes and settings will not be lost. Re-authorize the add-on by choosing Open Sidebar from the Extensions menu.
URL Inspection
Checks the indexation status from Google Search Console for a list of URLs.
Property: Select a Google Search Console property.
Coverage State: Google's coverage state for the URL e.g. Submitted and indexed.
Robots.txt State: Whether or not the page is blocked to Google by a robots.txt rule.
Indexing State: Whether or not the page blocks indexing through a noindex rule.
Last Crawl Time: Last time this URL was crawled by Google using the primary crawler. Absent if the URL was never crawled successfully.
Google Canonical: The URL of the page that Google selected as canonical. If the page was not indexed, this field is absent.
User Canonical: The URL that your page or site declares as canonical. If you did not declare a canonical URL, this field is absent.
Search Analytics
Gets performance data from Google Search Console for a list of URLs.
Property: Select a Google Search Console property.
Start Date: The date range start date.
End Date: The date range end date.
Date Split: Group metrics by day, month or year. Leave blank for date range total.
Impressions: How often someone saw a link to your site on Google.
Clicks: How often someone clicked a link from Google to your site.
CTR: Clicks divided by impressions.
Position: A relative ranking of the position of your link on Google, where 1 is the topmost position, 2 is the next position, and so on.
Impressions * Position: Impressions multiplied by (average) position. Useful when you need to perform further calculations in your spreadsheet.
The following optional filters are available by checking Use Filters.
Search Type: Filter results for a particular search type e.g. Web (default), Image, Video, etc.
Country: Filter results by country, as specified by 3-letter country code e.g. USA, GBR, AUS, etc.
Device: Filter results by device type e.g. DESKTOP, MOBILE or TABLET.
Search Appearance: Filter results for a particular search result feature e.g. ORGANIC_SHOPPING, REVIEW_SNIPPET etc.
Note, Country, Device and Search Appearance filters are only available once a Property has been selected.
Top Keywords
Gets the top keywords from Google Search Console for a list of URLs.
Property: Select a Google Search Console property.
Start Date: The date range start date.
End Date: The date range end date.
Keyword Limit: The number of keywords to return for each URL. Multiple keywords are returned in the same cell separated by a line break.
Exclude Terms (RegEx): An optional regex pattern to exclude matching keywords. If required, use a regex tester set to ECMAScript (JavaScript) to experiment with regex patterns.
Impressions: How often someone saw a link to your site on Google.
Clicks: How often someone clicked a link from Google to your site.
CTR: Clicks divided by impressions.
Position: A relative ranking of the position of your link on Google, where 1 is the topmost position, 2 is the next position, and so on.
Sort By: Whether to sort keywords by Clicks (default) or Impressions.
The following optional filters are available by checking Use Filters.
Search Type: Filter results for a particular search type e.g. Web (default), Image, Video, etc.
Country: Filter results by country, as specified by 3-letter country code e.g. USA, GBR, AUS, etc.
Device: Filter results by device type e.g. DESKTOP, MOBILE or TABLET.
Search Appearance: Filter results for a particular search result feature e.g. ORGANIC_SHOPPING, REVIEW_SNIPPET etc.
Note, Country, Device and Search Appearance filters are only available once a Property has been selected.
Moz
Moz functions connect to the Mozscape® API to retrieve metrics and link data.
Moz Authentication
You will require a free Mozscape Access ID and Secret Key to access these functions.
Access ID: Enter your Mozscape Access ID.
Secret Key: Enter your Mozscape Secret Key.
Save: Click to save your credentials. Credentials are securely stored on a per-user basis and will no longer be viewable once saved.
Note, once saved, you will be able to revoke Moz access by clicking "Reset Moz credentials" at the bottom of any Moz function.
Metrics
Gets metrics from Moz for a list of URLs.
Domain Authority (DA): A score from 1 to 100 representing the likelihood that this root domain will rank well in search engine result pages.
Page Authority (PA): A score from 1 to 100 representing the likelihood that this page will rank well in search engine result pages.
Spam Score: The spam score for the URL requested, or -1 if a spam score does not exist for that URL.
Domain Linking Domains: The number of unique root domains currently linking to this root domain. Links from the same root domain as the URL do not contribute to this count.
Domain Total Backlinks: The number of unique pages from a different root domain currently linking to this root domain.
Domain Nofollow Backlinks: The number of unique pages from a different root domain currently linking to this root domain with only nofollow links.
Domain Outlink Domains: The unique number of root domains linked to by the target root domain, or 0 if no pages on the root domain have ever been crawled.
Domain Total Outlinks: The unique number of pages linked to by the target root domain, or 0 if no pages on the root domain have ever been crawled.
Domain Nofollow Outlinks: The unique number of pages linked to by the target root domain using only nofollow links, or 0 if the page has never been crawled.
Page Linking Domains: The number of unique root domains currently linking to this page. Links from the same root domain as the URL do not contribute to this count.
Page Total Backlinks: The number of unique pages from a different root domain currently linking to this page.
Page Nofollow Backlinks: The number of unique pages from a different root domain currently linking to this page with only nofollow links.
Page Outlink Domains: The unique number of root domains linked to by the target page, or 0 if the page has never been crawled.
Page Total Outlinks: The unique number of pages linked to by the target page, or 0 if the page has never been crawled.
Page Nofollow Outlinks: The unique number of pages linked to by the target page using only nofollow links, or 0 if the page has never been crawled.
Top Links
Gets the top links from Moz for a list of URLs.
Target Scope: Select a target links scope e.g. Root Domain, Subdomain or Page.
Link Limit: The number of links to return for each URL. Multiple links are returned in the same cell separated by a line break.
Domain Authority (DA): A score from 1 to 100 representing the likelihood that this root domain will rank well in search engine result pages.
Page Authority (PA): A score from 1 to 100 representing the likelihood that this page will rank well in search engine result pages.
Spam Score: The spam score for the link, or -1 if a spam score does not exist for that link.
Linking To: The target URL for the link.
Anchor Text: The anchor text for the link.
Is Nofollow: Whether or not the link is a nofollow link.
Is Redirect: Whether or not the link is a redirect.
Is Canonical: Whether or not the link is a rel-canonical link.
First Seen: The date the link was first seen.
Last Seen: The date the link was most recently seen.
Disappeared: The date the link most recently disappeared.
Miscellaneous
The following miscellaneous functions are hidden by default but can be activated via the Settings screen.
Query Variables
Replaces column name variables with column letters in a Query() select statement. Column name variables must be in curly brackets e.g. {Clicks}.
This function works best when using a cell reference for the Query() select statement.
Column Names Row: A single row containing column headings to be updated in the select query.
Bolderize
Converts text wrapped in ** to a pre-bolded font. Useful for partial bolding in lookup formulas.
Remove ** Syntax: Whether or not to remove the ** syntax from the output.
Co-occurrence
Counts the unique occurrences of all items in a list.
Recipes
Saved functions can be managed from the Recipes screen.
Manage Store Functions
Each recipe contains one or more stored functions, highlighted in yellow. Hover over a stored function to reveal the following actions.
Edit: Loads the stored function in the Functions screen so it can be updated and re-saved.
Copy: Duplicates the stored function in the Functions screen so it can be saved as a new stored function.
Pause: Prevents the stored function from running when the recipe is run. Click again to unpause.
Delete: Deletes the stored function from the recipe.
Run, Export and Save
Each function provides actions to run and/or save, highlighted in blue.
Rename Recipe: Check to update the recipe name and description.
Recipe Name: Give the recipe a name.
Recipe Description: Give the recipe a description.
Run: Execute all active stored recipe functions in order.
Export: Generate an export snippet for the recipe. Opens in a pop-up. Useful for sharing recipes between spreadsheets and/or users.
Save: Save the recipe without executing it.
Import
Import a recipe into the current spreadsheet using a recipe snippet. This allows you to transfer recipes between spreadsheets or share recipes with other users.
Click Launch to open the import pop-up and paste the required recipe snippet. Recipe snippets are generated using the Export option on an existing recipe.
Recipe snippets must be valid JSON and contain a name, description (can be left blank) and functions array as shown in the above example.
Settings
Visible Functions
Show or hide chosen functions from the Functions screen.
URL Cache
Speed up repeat requests by caching URL responses for a set time.
Cache Minutes: The number of minutes to cache URL responses for from 0 to 360 (6 hours). Set to 0 to disable URL caching.
Changelog
v1.1 (2023-09-08)
New: Import recipe from file
New: Export recipe to file
New: Connection requests setting - Make multiple requests at the same time to increase speed
Fix: Expand rows option fix
Fix: Redirect rules remove domain prefix fix
v1.0 (2023-04-11)
Initial release