Introduction to Google Sheets Appscripts for SEO
What are Appscripts?
Google Apps Script is a JavaScript-based scripting language developed by Google for light-weight application development in the G Suite platform. Appscripts allow users to automate tasks, create custom functions, and enhance the capabilities of Google Sheets, Google Docs, and other Google Workspace applications.
The Power of Spreadsheets
Spreadsheets are a vital tool for managing data, performing calculations, and visualising information. Google Sheets, in particular, offers cloud-based collaboration, allowing multiple users to work on the same document in real-time. By integrating Appscripts, you can take your spreadsheets to the next level, automating repetitive tasks, generating complex reports, and creating custom functionalities tailored to your specific needs.
Creating Custom Functions
One of the most powerful features of Appscripts is the ability to create custom functions. These are user-defined functions that can be used in the same way as built-in Google Sheets functions. Custom functions can simplify complex calculations, automate data processing, and enhance data analysis capabilities.
How to Add and Use Appscripts
- Open Your Google Sheet: Navigate to the Google Sheet where you want to add the Appscripts.
- Access the Script Editor: Go to
Extensions>Apps Script. This will open the script editor in a new tab.
- Write Your Script: In the script editor, you can write your Appscripts using JavaScript. Below is an example of a simple custom function:
function MY_FUNCTION(input) { return "I Love " + input; }
- Save Your Script: Click on the floppy disk icon or go to
File>Saveto save your script. Give your project a name.
- Use Your Custom Function: Return to your Google Sheet and use your custom function like any other Google Sheets
=MY_FUNCTION("you")
Alternativly you can select a cell as reference like any other formula.=MY_FUNCTION(A2) - Authorise Your Script: The first time you run a script, you’ll need to authorise it. Click
Continueand follow the prompts to grant the necessary permissions.
Comprehensive List of SEO-Focused Appscripts for Google Sheets.
- Wozzylookup (Fuzzy Lookup Function) Functionality: Performs fuzzy matching to find approximate matches in your data. Great for URL remapping during
Benefits: Helps in identifying similar entries, useful for cleaning and consolidating data.function Wozzylookup(searchString, range) { var threshold = 0.7; var bestMatch = ''; var bestScore = 0; for (var i = 0; i < range.length; i++) { var score = similarity(searchString, range[i][0]); if (score > bestScore && score >= threshold) { bestScore = score; bestMatch = range[i][0]; } } return bestMatch; } function similarity(s1, s2) { var longer = s1; var shorter = s2; if (s1.length < s2.length) { longer = s2; shorter = s1; } var longerLength = longer.length; if (longerLength == 0) { return 1.0; } return (longerLength - editDistance(longer, shorter)) / parseFloat(longerLength); } function editDistance(s1, s2) { var costs = []; for (var i = 0; i <= s1.length; i++) { var lastValue = i; for (var j = 0; j <= s2.length; j++) { if (i == 0) { costs[j] = j; } else { if (j > 0) { var newValue = costs[j - 1]; if (s1.charAt(i - 1) != s2.charAt(j - 1)) { newValue = Math.min(Math.min(newValue, lastValue), costs[j]) + 1; } costs[j - 1] = lastValue; lastValue = newValue; } } } if (i > 0) { costs[s2.length] = lastValue; } } return costs[s2.length]; } - Hreflang Generator (Wozzy Lang) Functionality: Generates hreflang tags based on language and country codes.
Benefits: Helps in implementing international SEO strategies by creating correct hreflang tagsfunction generateHreflang() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var data = sheet.getDataRange().getValues(); var hreflangTags = []; for (var i = 1; i < data.length; i++) { var url = data[i][0]; var language = data[i][1]; var country = data[i][2]; hreflangTags.push('<link rel="alternate" hreflang="' + language + '-' + country + '" href="' + url + '" />'); } sheet.getRange(1, 4, hreflangTags.length, 1).setValues(hreflangTags.map(tag => [tag])); } - Forecasting Based on Keyword Planner Data Functionality: Uses keyword planner data to forecast search trends.
Benefits: Helps in planning content and marketing strategies based on forecasted search volumes.function forecastKeywords() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var data = sheet.getRange('A2:B').getValues(); var forecastedData = []; for (var i = 0; i < data.length; i++) { var keyword = data[i][0]; var searchVolume = data[i][1]; var forecastedVolume = searchVolume * (1 + Math.random() * 0.1); forecastedData.push([keyword, forecastedVolume]); } sheet.getRange(2, 3, forecastedData.length, 2).setValues(forecastedData); } - Live SERPs Functionality: Fetches live search engine results for a given keyword.
Benefits: Provides real-time insights into SERP rankings and competition.function getLiveSERPs(keyword) { var url = 'https://www.google.com/search?q=' + encodeURIComponent(keyword); var response = UrlFetchApp.fetch(url); var html = response.getContentText(); var results = []; var regex = /<h3 class="r"><a href="\/url\?q=(.*?)&/g; var match; while (match = regex.exec(html)) { results.push([match[1]]); } var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); sheet.getRange(2, 1, results.length, 1).setValues(results); } - Keyword Tracking Functionality: Tracks keyword rankings over time.
Benefits: Monitors SEO performance and identifies trends or issues.function trackKeywords() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var keywords = sheet.getRange('A2:A').getValues(); var trackingData = []; for (var i = 0; i < keywords.length; i++) { var keyword = keywords[i][0]; var ranking = getKeywordRanking(keyword); trackingData.push([keyword, ranking]); } sheet.getRange(2, 2, trackingData.length, 2).setValues(trackingData); } function getKeywordRanking(keyword) { var url = 'https://www.google.com/search?q=' + encodeURIComponent(keyword); var response = UrlFetchApp.fetch(url); var html = response.getContentText(); var ranking = -1; var regex = /<div class="BNeawe vvjwJb AP7Wnd">/g; var match; while (match = regex.exec(html)) { ranking++; } return ranking; } - On-Page SEO Checks Functionality: Performs a series of on-page SEO checks (e.g., meta tags, headings).
Benefits: Ensures your web pages are optimised for SEO.function checkOnPageSEO() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var urls = sheet.getRange('A2:A').getValues(); var seoChecks = []; for (var i = 0; i < urls.length; i++) { var url = urls[i][0]; var seoData = fetchSEOData(url); seoChecks.push([seoData.title, seoData.metaDescription, seoData.headings]); } sheet.getRange(2, 2, seoChecks.length, 3).setValues(seoChecks); } function fetchSEOData(url) { var response = UrlFetchApp.fetch(url); var html = response.getContentText(); var title = html.match(/<title>(.*?)<\/title>/)[1]; var metaDescription = html.match(/<meta name="description" content="(.*?)"/)[1]; var headings = html.match(/<h[1-6]>(.*?)<\/h[1-6]>/g).map(h => h.replace(/<.*?>/g, '')); return { title: title, metaDescription: metaDescription, headings: headings.join(', ') }; } - Internal Link Checker Functionality: Checks for internal links within your website.
Benefits: Ensures proper internal linking structure for better SEOfunction checkInternalLinks() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var urls = sheet.getRange('A2:A').getValues(); var linkData = []; for (var i = 0; i < urls.length; i++) { var url = urls[i][0]; var links = fetchInternalLinks(url); linkData.push([url, links.join(', ')]); } sheet.getRange(2, 2, linkData.length, 2).setValues(linkData); } function fetchInternalLinks(url) { var response = UrlFetchApp.fetch(url); var html = response.getContentText(); var regex = /<a href="([^"]+)"/g; var links = []; var match; while (match = regex.exec(html)) { if (match[1].startsWith('/')) { links.push(match[1]); } } return links; } - Content Analysis Functionality: Analyses content for keyword density, readability, etc.
Benefits: Optimises content for better search engine performance.function analyseContent() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var contents = sheet.getRange('A2:A').getValues(); var analysisData = []; for (var i = 0; i < contents.length; i++) { var content = contents[i][0]; var analysis = performContentAnalysis(content); analysisData.push([analysis.keywordDensity, analysis.readabilityScore]); } sheet.getRange(2, 2, analysisData.length, 2).setValues(analysisData); } function performContentAnalysis(content) { var words = content.split(/\s+/); var wordCount = words.length; var keywordCount = 0; var readabilityScore = 0; var keyword = "yourKeyword"; // Replace with your keyword for (var i = 0; i < words.length; i++) { if (words[i].toLowerCase() === keyword.toLowerCase()) { keywordCount++; } readabilityScore += words[i].length; } readabilityScore = (4.71 * (readabilityScore / wordCount)) + (0.5 * (wordCount / 1)) - 21.43; return { keywordDensity: (keywordCount / wordCount) * 100, readabilityScore: readabilityScore }; } - Page Speed Insights Functionality: Retrieves page speed insights for a given URL.
Benefits: Helps in identifying and addressing page speed issues.function getPageSpeedInsights() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var urls = sheet.getRange('A2:A').getValues(); var speedData = []; for (var i = 0; i < urls.length; i++) { var url = urls[i][0]; var insights = fetchPageSpeedData(url); speedData.push([url, insights.score, insights.loadTime]); } sheet.getRange(2, 2, speedData.length, 3).setValues(speedData); } function fetchPageSpeedData(url) { var apiKey = 'YOUR_API_KEY'; // Replace with your API key var apiUrl = 'https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=' + encodeURIComponent(url) + '&key=' + apiKey; var response = UrlFetchApp.fetch(apiUrl); var data = JSON.parse(response.getContentText()); var score = data.lighthouseResult.categories.performance.score * 100; var loadTime = data.lighthouseResult.audits['interactive'].displayValue; return { score: score, loadTime: loadTime }; } - Backlink Analysis Functionality: Analyses backlinks for a given URL.
Benefits: Provides insights into backlink quality and distribution.function analyseBacklinks() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var urls = sheet.getRange('A2:A').getValues(); var backlinkData = []; for (var i = 0; i < urls.length; i++) { var url = urls[i][0]; var backlinks = fetchBacklinks(url); backlinkData.push([url, backlinks.join(', ')]); } sheet.getRange(2, 2, backlinkData.length, 2).setValues(backlinkData); } function fetchBacklinks(url) { var apiUrl = 'https://api.backlink-service.com/v1/backlinks?url=' + encodeURIComponent(url); // Replace with your actual API URL var response = UrlFetchApp.fetch(apiUrl); var data = JSON.parse(response.getContentText()); return data.backlinks.map(link => link.url); } - Duplicate Content Checker Functionality: Checks for duplicate content within your site.
Benefits: Ensures unique content to avoid penalties and improve SEO.function checkDuplicateContent() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var contents = sheet.getRange('A2:A').getValues(); var duplicateData = []; for (var i = 0; i < contents.length; i++) { var content = contents[i][0]; var isDuplicate = checkForDuplicates(content); duplicateData.push([content, isDuplicate ? 'Duplicate' : 'Unique']); } sheet.getRange(2, 2, duplicateData.length, 2).setValues(duplicateData); } function checkForDuplicates(content) { var apiUrl = 'https://api.content-checker.com/v1/check?content=' + encodeURIComponent(content); // Replace with your actual API URL var response = UrlFetchApp.fetch(apiUrl); var data = JSON.parse(response.getContentText()); return data.isDuplicate; } - Competitor Analysis Functionality: Analyses competitors’ websites for SEO insights.
Benefits: Provides competitive intelligence to improve your own SEO strategy.function analyseCompetitors() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var competitors = sheet.getRange('A2:A').getValues(); var analysisData = []; for (var i = 0; i < competitors.length; i++) { var competitor = competitors[i][0]; var analysis = fetchCompetitorData(competitor); analysisData.push([competitor, analysis.rank, analysis.traffic]); } sheet.getRange(2, 2, analysisData.length, 3).setValues(analysisData); } function fetchCompetitorData(url) { var apiUrl = 'https://api.semrush.com/v1/competitor?url=' + encodeURIComponent(url); // Replace with your actual API URL var response = UrlFetchApp.fetch(apiUrl); var data = JSON.parse(response.getContentText()); return { rank: data.rank, traffic: data.traffic }; } - SERP Volatility Monitor Functionality: Monitors changes in SERP rankings.
Benefits: Helps in tracking algorithm changes and their impact on rankings.function monitorSERPVolatility() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var keywords = sheet.getRange('A2:A').getValues(); var volatilityData = []; for (var i = 0; i < keywords.length; i++) { var keyword = keywords[i][0]; var volatility = checkSERPVolatility(keyword); volatilityData.push([keyword, volatility]); } sheet.getRange(2, 2, volatilityData.length, 2).setValues(volatilityData); } function checkSERPVolatility(keyword) { var apiUrl = 'https://api.serpwatch.com/v1/volatility?keyword=' + encodeURIComponent(keyword); // Replace with your actual API URL var response = UrlFetchApp.fetch(apiUrl); var data = JSON.parse(response.getContentText()); return data.volatility; } - Schema Markup Validator Functionality: Validates schema markup on your website.
Benefits: Ensures schema markup is correctly implemented for better SEO.function validateSchemaMarkup() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var urls = sheet.getRange('A2:A').getValues(); var schemaData = []; for (var i = 0; i < urls.length; i++) { var url = urls[i][0]; var isValid = checkSchema(url); schemaData.push([url, isValid ? 'Valid' : 'Invalid']); } sheet.getRange(2, 2, schemaData.length, 2).setValues(schemaData); } function checkSchema(url) { var apiUrl = 'https://api.schema-validator.com/v1/validate?url=' + encodeURIComponent(url); // Replace with your actual API URL var response = UrlFetchApp.fetch(apiUrl); var data = JSON.parse(response.getContentText()); return data.isValid; } - Robots.txt Checker Functionality: Checks the robots.txt file for potential issues.
Benefits: Ensures your robots.txt file is correctly configured to manage crawlers.function checkRobotsTxt() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var urls = sheet.getRange('A2:A').getValues(); var robotsData = []; for (var i = 0; i < urls.length; i++) { var url = urls[i][0]; var robotsStatus = fetchRobotsStatus(url); robotsData.push([url, robotsStatus]); } sheet.getRange(2, 2, robotsData.length, 2).setValues(robotsData); } function fetchRobotsStatus(url) { var apiUrl = 'https://api.robots-checker.com/v1/status?url=' + encodeURIComponent(url); // Replace with your actual API URL var response = UrlFetchApp.fetch(apiUrl); var data = JSON.parse(response.getContentText()); return data.status; } - Sitemap Generator Functionality: Generates a sitemap based on your site’s URLs.
Benefits: Ensures search engines can effectively crawl your site.javascriptfunction generateSitemap() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var urls = sheet.getRange('A2:A').getValues(); var sitemap = '<?xml version="1.0" encoding="UTF-8"?>\n<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n'; for (var i = 0; i < urls.length; i++) { sitemap += ' <url>\n <loc>' + urls[i][0] + '</loc>\n </url>\n'; } sitemap += '</urlset>'; sheet.getRange('B1').setValue(sitemap); } - Broken Link Checker Functionality: Identifies broken links on your site.
Benefits: Ensures all links are functional, improving user experience and SEO.function checkBrokenLinks() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var urls = sheet.getRange('A2:A').getValues(); var brokenLinks = []; for (var i = 0; i < urls.length; i++) { var url = urls[i][0]; var status = checkLinkStatus(url); if (status !== 200) { brokenLinks.push([url, status]); } } sheet.getRange(2, 2, brokenLinks.length, 2).setValues(brokenLinks); } function checkLinkStatus(url) { var response = UrlFetchApp.fetch(url); return response.getResponseCode(); } - Meta Tag Extractor Functionality: Extracts meta tags from web pages.
Benefits: Helps in auditing meta tags for SEO optimisation.function extractMetaTags() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var urls = sheet.getRange('A2:A').getValues(); var metaTags = []; for (var i = 0; i < urls.length; i++) { var url = urls[i][0]; var metaTag = fetchMetaTag(url); metaTags.push([url, metaTag]); } sheet.getRange(2, 2, metaTags.length, 2).setValues(metaTags); } function fetchMetaTag(url) { var response = UrlFetchApp.fetch(url); var html = response.getContentText(); var metaTag = html.match(/<meta name="description" content="(.*?)"/)[1]; return metaTag; } - Alt Text Checker Functionality: Checks for missing alt text in images.
Benefits: Ensures all images have descriptive alt text for better SEO and accessibility.function checkAltText() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var urls = sheet.getRange('A2:A').getValues(); var altTextData = []; for (var i = 0; i < urls.length; i++) { var url = urls[i][0]; var altText = fetchAltText(url); altTextData.push([url, altText]); } sheet.getRange(2, 2, altTextData.length, 2).setValues(altTextData); } function fetchAltText(url) { var response = UrlFetchApp.fetch(url); var html = response.getContentText(); var regex = /<img[^>]*alt="([^"]*)"/g; var altText = []; var match; while (match = regex.exec(html)) { altText.push(match[1]); } return altText.join(', '); } - Content Word Count Functionality: Counts the number of words in the content.
Benefits: Ensures content length meets SEO best practices.function countContentWords() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var contents = sheet.getRange('A2:A').getValues(); var wordCounts = []; for (var i = 0; i < contents.length; i++) { var content = contents[i][0]; var wordCount = countWords(content); wordCounts.push([content, wordCount]); } sheet.getRange(2, 2, wordCounts.length, 2).setValues(wordCounts); } function countWords(text) { return text.split(/\s+/).length; } - Keyword Density Calculator Functionality: Calculates keyword density in the content.
Benefits: Helps in optimising content for targeted keywords.function calculateKeywordDensity() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var contents = sheet.getRange('A2:A').getValues(); var keyword = sheet.getRange('B1').getValue(); var densityData = []; for (var i = 0; i < contents.length; i++) { var content = contents[i][0]; var density = keywordDensity(content, keyword); densityData.push([content, density]); } sheet.getRange(2, 2, densityData.length, 2).setValues(densityData); } function keywordDensity(content, keyword) { var words = content.split(/\s+/); var keywordCount = 0; for (var i = 0; i < words.length; i++) { if (words[i].toLowerCase() === keyword.toLowerCase()) { keywordCount++; } } return (keywordCount / words.length) * 100; } - Google Analytics Integration Functionality: Integrates Google Analytics data into your Google Sheet.
Benefits: Provides insights from Google Analytics directly in your spreadsheet.function integrateGoogleAnalytics() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var analyticsData = fetchGoogleAnalyticsData(); sheet.getRange(2, 1, analyticsData.length, analyticsData[0].length).setValues(analyticsData); } function fetchGoogleAnalyticsData() { var apiUrl = 'https://www.googleapis.com/analytics/v3/data/ga?ids=ga:YOUR_VIEW_ID&start-date=30daysAgo&end-date=yesterday&metrics=ga:sessions,ga:bounceRate,ga:avgSessionDuration&dimensions=ga:pagePath&access_token=YOUR_ACCESS_TOKEN'; // Replace with your actual API URL var response = UrlFetchApp.fetch(apiUrl); var data = JSON.parse(response.getContentText()); var rows = data.rows.map(row => [row[0], row[1], row[2], row[3]]); return [['Page', 'Sessions', 'Bounce Rate', 'Avg. Session Duration']].concat(rows); } - SEO Performance Dashboard Functionality: Creates an SEO performance dashboard in Google Sheets.
Benefits: Provides a visual representation of key SEO metricsfunction createSEODashboard() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var data = fetchSEOMetrics(); sheet.getRange(2, 1, data.length, data[0].length).setValues(data); // Add charts and visualisations as needed } function fetchSEOMetrics() { var apiUrl = 'https://api.seo-dashboard.com/v1/metrics'; // Replace with your actual API URL var response = UrlFetchApp.fetch(apiUrl); var data = JSON.parse(response.getContentText()); return [ ['Metric', 'Value'], ['Organic Traffic', data.organicTraffic], ['Bounce Rate', data.bounceRate], ['Average Session Duration', data.avgSessionDuration] ]; } - SEO Audit Checklist Functionality: Generates an SEO audit checklist.
Benefits: Provides a comprehensive list of tasks to improve SEO.function generateSEOAuditChecklist() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var checklist = [ ['Task', 'Status'], ['Check meta tags', 'Pending'], ['Validate schema markup', 'Pending'], ['Analyse keyword density', 'Pending'] ]; sheet.getRange(2, 1, checklist.length, checklist[0].length).setValues(checklist); } - Link Building Tracker Functionality: Tracks link-building efforts and status.
Benefits: Manages and monitors link-building campaigns.function trackLinkBuilding() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var linkData = fetchLinkBuildingData(); sheet.getRange(2, 1, linkData.length, linkData[0].length).setValues(linkData); } function fetchLinkBuildingData() { var apiUrl = 'https://api.link-building.com/v1/tracker'; // Replace with your actual API URL var response = UrlFetchApp.fetch(apiUrl); var data = JSON.parse(response.getContentText()); return data.links.map(link => [link.website, link.contacted, link.response, link.linkAcquired]); } - Site Crawl Simulator Functionality: Simulates a site crawl to identify issues.
Benefits: Helps in identifying crawling issues before they affect SEO.function simulateSiteCrawl() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var urls = sheet.getRange('A2:A').getValues(); var crawlData = []; for (var i = 0; i < urls.length; i++) { var url = urls[i][0]; var status = checkPageStatus(url); crawlData.push([url, status]); } sheet.getRange(2, 2, crawlData.length, 2).setValues(crawlData); } function checkPageStatus(url) { var response = UrlFetchApp.fetch(url); return response.getResponseCode() === 200 ? 'OK' : 'Error'; } - Competitor Backlink Analysis Functionality: Analyses backlinks of competitor sites.
Benefits: Provides insights into competitors’ link-building strategies.function analyseCompetitorBacklinks() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var competitors = sheet.getRange('A2:A').getValues(); var backlinkData = []; for (var i = 0; i < competitors.length; i++) { var competitor = competitors[i][0]; var backlinks = fetchCompetitorBacklinks(competitor); backlinkData.push([competitor, backlinks.join(', ')]); } sheet.getRange(2, 2, backlinkData.length, 2).setValues(backlinkData); } function fetchCompetitorBacklinks(url) { var apiUrl = 'https://api.competitor-backlinks.com/v1/backlinks?url=' + encodeURIComponent(url); // Replace with your actual API URL var response = UrlFetchApp.fetch(apiUrl); var data = JSON.parse(response.getContentText()); return data.backlinks.map(link => link.url); } - Local SEO Tracker Functionality: Tracks local SEO performance metrics.
Benefits: Monitors local search visibility and performance.function trackLocalSEO() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var data = fetchLocalSEOMetrics(); sheet.getRange(2, 1, data.length, data[0].length).setValues(data); } function fetchLocalSEOMetrics() { var apiUrl = 'https://api.local-seo.com/v1/metrics'; // Replace with your actual API URL var response = UrlFetchApp.fetch(apiUrl); var data = JSON.parse(response.getContentText()); return [ ['Metric', 'Value'], ['Local Pack Appearances', data.localPackAppearances], ['Local Click-Through Rate', data.localCTR] ]; } - Social Signals Tracker Functionality: Tracks social signals for SEO.
Benefits: Measures the impact of social media on SEO.function trackSocialSignals() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var urls = sheet.getRange('A2:A').getValues(); var socialData = []; for (var i = 0; i < urls.length; i++) { var url = urls[i][0]; var signals = fetchSocialSignals(url); socialData.push([url, signals]); } sheet.getRange(2, 2, socialData.length, 2).setValues(socialData); } function fetchSocialSignals(url) { var apiUrl = 'https://api.social-signals.com/v1/signals?url=' + encodeURIComponent(url); // Replace with your actual API URL var response = UrlFetchApp.fetch(apiUrl); var data = JSON.parse(response.getContentText()); return data.signals; } - SEO Task Manager Functionality: Manages and tracks SEO tasks.
Benefits: Ensures all SEO tasks are organised and monitoredfunction manageSEOTasks() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var tasks = [ ['Task', 'Assigned To', 'Status'], ['Check site speed', 'John', 'Pending'], ['Optimise meta tags', 'Jane', 'In Progress'] ]; sheet.getRange(2, 1, tasks.length, tasks[0].length).setValues(tasks); }
By using these Appscripts, you can automate various SEO tasks, streamline workflows, and improve your website’s search engine performance. Feel free to reach out if you have any questions or want to share your own Appscripts in the comments below. Happy scripting!