| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
1 | DEPT || Google Apps Script Examples || WTSFest Berlin | ||||||||
2 | |||||||||
3 | Introduction (click + to expand/collapse) | ||||||||
4 | • | The goal of this sheet is to give you first familiarity with Google Apps Scripts and how you can use it for SEO. | |||||||
5 | • | It includes example scripts to show you the basic Apps Script functionality as well as some concrete SEO use cases. | |||||||
6 | • | First, make a copy of the sheet to go from view to edit mode. | |||||||
7 | • | If you are new to Apps Script, feel free to follow the examples in the order listed below. › Go to Extensions > Apps Script to open the environment where you can find all Apps Script files. › Also check out the different tabs in the Google Sheet that belong to some script examples. | |||||||
8 | • | Go through the examples and try to retrace how the scripts work. › If you are in doubt, ask ChatGPT to explain the scripts to you. Don't forget to remove sensitive data like API keys if you paste scripts into ChatGPT. › It usually does a great job explaining scripts in simple terms. | |||||||
9 | |||||||||
10 | Order | Type | Use Case | Why to use it? | How to use it yourself? | Script File | Tab(s) | Slides in Talk | Prerequisites? |
11 | 1) | Concept: Functions | Learning | • Run your first Apps Script functions. • See how to reuse function in your code. | Go to the script file 'Code.gs': (1) Click "Execution log" (2) Select 'myFirstFunction' and press 'Run'. (3) Give the scripts permission to run. (4) Select 'mySecondFunction' and press 'Run'. (5) Retrace what the logic in each function did. | Code.gs | - | only script file | Slide 46 to Slide 57 | none |
12 | 2) | Concept: Run & Display Options in Apps Script | Learning | • Understand the options to run your scripts. › Run scripts in the editor. › Run scripts with custom buttons or menus. • Understand the options to display the script output. › See output in the editor's execution log. › Send the output in alerts. › Write the output to a sheet. › Return it in a cell by using custom functions like formulas. | Go to the tab 'alert-write': (1) Click on the green button and enter a name. (2) Click on the menu button "Custom Menu" › "Get name and append". (3) Both triggered the same function 'alertWithInput'. (4) Right-click on the green button, then on the 3 dots, then on 'Assign script'. Go to the script file 'alert-write': (1) Review the functions 'alertWithInput' and 'appendResponseToSheet'. (2) Retrace what the logic in each function did. (3) Select 'alertOne' and press 'Run' and check your sheet. Do the same for 'alertTwo'. Go to the script file 'custom-menu': (1) Find the code line where 'alertWithInput' is added to the menu. (2) Change some names in the menu, save the file, reload the sheet. Go to the tab 'custom-functions': (1) Have a look at the different formulas used in the green cells. (2) Go to the script file 'custom-functions'. (3) Compare the functions in the script to the formulas in the tab. (4) Try to retrace the logic in the script. | • alert-write.gs • custom-menu.gs • custom-functions.gs | • alert-write • custom-functions | Slide 58 to Slide 70 | none |
13 | 3) | Concept: Basic JavaScript Syntax | Learning | • Get to know the basic syntax concepts of JavaScript. • Ask ChatGPT for explanations to understand each piece of syntax. | Go to the script 'basic-js-syntax.gs': (1) Read the comment lines starting with "//". (2) Run the different available functions and try to understand the console.log() outputs. (3) Feel free to play around with the code. | basic-js-syntax.gs | - | only script file | Slide 112 to Slide 120 | none |
14 | 4) | Concept: Control Flow & Debugging | Learning | • Understand how your computer runs the code that ChatGPT gives you. • Train your debugging skills. | Go to the tab 'control-flow': (1) Delete the current text. (2) Click on the menu button "Custom Menu" › "Visualise control flow". (3) Go to the script file 'control-flow'. (4) Try to retrace the logic in the script. (5) Use the debugger with breakpoints or console.log() to train your debugging skills. (6) Try to understand the control flow and how the script is processed top to bottom. | control-flow.gs | control-flow | Slide 74 to Slide 102 | none |
15 | 5) | API Example SurferSEO | Content Briefing Creation at Scale | • Create SurferSEO content editors in bulk from a keyword targeting list. • Receive the content editor links written into your Google Sheet. | Do you have an API key? Go to tab ‘surfer-seo-briefings’: (1) Enter your keywords and set status in column A to ‘2 - SurferSEO API | Create Briefings’. (2) Enter your SurferSEO API key and country in the code on lines 1 & 2. (3) Click on the menu button "Custom Menu" › ‘SurferSEO: Create Briefings'. You don't have an API key? Copy/paste the script to ChatGPT and ask it to explain the syntax and coding concepts. | surfer-seo-briefings.gs | surfer-seo-briefings | Slide 20 to Slide 22 | SurferSEO subscription & API key |
16 | 6) | API Example Ryte | Import of Crawl Data from Tool APIs | • Export crawl data from Ryte to keep your analysis sheets up-to-date. • Define the format once and replicate the export by pressing a button. | Do you have an API key & project ID? Go to code file 'ryte-export.gs': (1) Add your Ryte API key & project ID on lines 23 & 24. (2) Click on the menu button "Custom Menu" › “Ryte: Get Crawl Report”. (3) Update ‘payloadCrawl’ if you want to export different fields. You can get the necessary fields from a report in the Ryte UI by clicking on "API call". You don't have an API key & project ID? Copy/paste the script to ChatGPT and ask it to explain the syntax and coding concepts. | ryte-export.gs | ryte-export | Slide 23 to Slide 26 | Ryte subscription & API key |
17 | 7) | API Example SEMrush | Bulk Seed Keyword Expansion with Keyword Tool API | • Export the SEMrush 'Related Queries’ report to expand seed keywords. • API appends reports for each seed keyword to one output sheet. | Go to tab ‘semrush-input’: (1) Add your API key, select your country and add your keywords, (2) Click on the menu button "Custom Menu" › 'SEMrush: Get Related Keywords’, (3) Check outputs in tab ‘semrush-output’ | semrush-keywords.gs | • semrush-input • semrush-output | Slide 27 to Slide 28 | SEMrush subscription & API key |
18 | 8) | API Example DataForSEO | Custom SERP Analysis with DataForSEO | • Use the DataForSEO SERP API to scrape SERP info for a keyword list. • Get SERP info like list of SERP features per keyword. • Calculate custom metrics like pixel rank. | bit.ly/dept-pixel-rank | bit.ly/dept-pixel-rank | bit.ly/dept-pixel-rank | Slide 29 to Slide 32 https://speakerdeck.com/johannamaier/ pixel-rank-the-better-metric-for-your-ranking | DataForSEO account and credits (1$ test budget for new accounts) |
19 | 9) | Execute Functions & Formulas On-Demand | Check Status Codes | • Check HTTP status codes of URLs with a script. • Use a custom menu to control when a function is run. • Use a script to turn formulas into text and vice versa. | Go to tab ‘status-codes’: (1) Click "Custom Menu" › 'Get HTTP status codes' in menu to refresh status codes in column B. (2) Try "Custom Menu" › ' ‘Copy formulas’ options to update column C. | status-codes.gs | status-codes | Slide 34 to Slide 36 | none |
20 | 10) | API Example DataForSEO | DataForSEO SERP API | • Walkthrough: How to write a Apps Script together with ChatGPT? • Walkthrough: How to connect to an API with Apps Script? | see article in Oncrawl blog to be published soon | dataforseo-serp-api.gs | • DataForSEO Settings • DataForSEO Output | see article in Oncrawl blog to be published soon | DataForSEO account and credits (1$ test budget for new accounts) |
21 | |||||||||
22 | |||||||||
23 | |||||||||
24 | |||||||||
25 | |||||||||
26 | |||||||||
27 | |||||||||
28 | |||||||||
29 | |||||||||
30 | |||||||||
31 | |||||||||
32 | |||||||||
33 | |||||||||
34 | |||||||||
35 | |||||||||
36 | |||||||||
37 | |||||||||
38 | |||||||||
39 | |||||||||
40 | |||||||||
41 | |||||||||
42 | |||||||||
43 | |||||||||
44 | |||||||||
45 | |||||||||
46 | |||||||||
47 | |||||||||
48 | |||||||||
49 | |||||||||
50 | |||||||||
51 | |||||||||
52 | |||||||||
53 | |||||||||
54 | |||||||||
55 | |||||||||
56 | |||||||||
57 | |||||||||
58 | |||||||||
59 | |||||||||
60 | |||||||||
61 | |||||||||
62 | |||||||||
63 | |||||||||
64 | |||||||||
65 | |||||||||
66 | |||||||||
67 | |||||||||
68 | |||||||||
69 | |||||||||
70 | |||||||||
71 | |||||||||
72 | |||||||||
73 | |||||||||
74 | |||||||||
75 | |||||||||
76 | |||||||||
77 | |||||||||
78 | |||||||||
79 | |||||||||
80 | |||||||||
81 | |||||||||
82 | |||||||||
83 | |||||||||
84 | |||||||||
85 | |||||||||
86 | |||||||||
87 | |||||||||
88 | |||||||||
89 | |||||||||
90 | |||||||||
91 | |||||||||
92 | |||||||||
93 | |||||||||
94 | |||||||||
95 | |||||||||
96 | |||||||||
97 | |||||||||
98 | |||||||||
99 | |||||||||
100 | |||||||||