Google Apps Script: Using Google Sheets hidden superpower for SEO

Chances are that you regularly use Google Sheets. But have you been using its connected code editor: Google Apps Script? Let’s do it together step by step along some SEO use cases. This talk will cover all you need to get started with Google Apps Script.

Transcript

1. 1 #WTSFest 1 #WTSFest
2. 2 #WTSFest 2 #WTSFest Women in Tech SEO Festival 07 June 2024 | Berlin
3. 3 #WTSFest 3 #WTSFest Google Apps Script Using Google Sheets Hidden Superpower Johanna Maier
4. 4 #WTSFest 01 Why use Google Apps Script? 02 Environment & Functions 03 Flow, Debugging & Prompting 04 Appendix: Checklists & Resources Agenda Johanna Maier
5. 5 #WTSFest 5 • 2019: SEO traineeship • Lots of data wrangling in Google Sheets. • 2021: coding bootcamp • Automation fangirl Johanna Maier Senior SEO Consultant at DEPT®
6. 6 #WTSFest 6 #WTSFest Why use Google Apps Script? Why use Google Apps Script? 01 Johanna Maier
7. 7 #WTSFest I love Google Sheets. But formulas also have their limitations. Johanna Maier Why use Google Apps Script?
8. 8 #WTSFest Johanna Maier Why use Google Apps Script? automation tools you might know already
9. 9 #WTSFest Johanna Maier Why use Google Apps Script? no-code vs. low-code (🚀 ChatGPT)
10. 10 #WTSFest Google Apps Script 🗝 unlocks the full potential of Google’s Workspace or any tools/sources with an API. Johanna Maier Why use Google Apps Script?
11. 11 #WTSFest You can access Google Apps Script from many places.
12. 12 #WTSFest Google Apps Script is for ‘non-traditional programmers’ or ‘citizen developers’. Johanna Maier Why use Google Apps Script? Sources: medium.com/@Nontechpreneur & workspace.google.com
13. 13 #WTSFest Don’t you often already use complex formulas in your workflows? Johanna Maier Why use Google Apps Script? 🚀
14. 14 #WTSFest #WTSFest
15. 15 #WTSFest #WTSFest Difficult to understand or update. 🤪
16. 16 #WTSFest #WTSFest LONG loading times while opening. ⏳
17. 17 #WTSFest Formulas work fine in many cases. When does a script work better? Johanna Maier Why use Google Apps Script?
18. 18 #WTSFest Johanna Maier Why use Google Apps Script? When can a script provide value over formulas? 1) Avoid messy formulas ◦ Scripts are easier to read & debug. ◦ Updates are more straightforward. 2) Avoid long loading times ◦ Formulas run instantly / on open. ◦ Scripts can run selectively and perform better with large data. 3) Integrate Google Workspace & Tool APIs ◦ Scripts send data between Google files. ◦ Scripts pull in data from tool APIs. 🤪
19. 19 #WTSFest Johanna Maier Why use Google Apps Script? 'Upload keyword targeting data to a BigQuery project.' 'Create new content briefings from keywords in Google Sheets.' 'Pull data from a tool API and write it to a Google Sheet.'
20. 20 #WTSFest 20 Johanna Maier Why use Google Apps Script? Content Briefing Creation at Scale Johanna Maier USE CASE surfer-seo-briefings.gs || #WTSFest surfer-seo-briefings Situation You want to transfer keyword targeting info into your preferred content briefing format. Approach • Send keywords to a tool API (e.g. SurferSEO) to create briefings in bulk. • Create briefings from a Google Docs template and fill placeholders with briefing info. Script Example bit.ly/dept-wtsfest-berlin
21. 21 #WTSFest Content Briefings in Bulk with a Tool API surfer-seo-briefings surfer-seo-briefings.gs
22. 22 #WTSFest Content Briefings in Bulk with Docs Template Source: Marcus Kästner
23. 23 #WTSFest 23 Johanna Maier Why use Google Apps Script? Import from Tool APIs: Crawl Data Situation You want to export crawl data from a tool to keep your analysis sheets up-to-date. Approach Use a cloud crawling tool with an API connector, schedule your site crawls and write the needed data right into your sheets. Script Example bit.ly/dept-wtsfest-berlin Johanna Maier USE CASE #WTSFest ryte-export ryte-export.gs
24. 24 #WTSFest Import Crawl Data via Tool API ryte-export ryte-export.gs
25. 25 #WTSFest Import Crawl Data via Tool API ryte-export ryte-export.gs
26. 26 #WTSFest Import Crawl Data via Tool API ryte-export ryte-export.gs
27. 27 #WTSFest 27 Johanna Maier Why use Google Apps Script? Bulk Keyword Reports Johanna Maier USE CASE #WTSFest semrush-input semrush-keywords.gs semrush-output Situation You need ‘Related Queries’ reports from SEMrush to expand a list of seed keywords. Approach Use the SEMrush developer API to append keyword reports for each seed keyword to one output sheet. Script Example bit.ly/dept-wtsfest-berlin
28. 28 #WTSFest Keyword Expansion in Bulk with Tool API semrush-input semrush-keywords.gs semrush-output
29. 29 #WTSFest 29 Johanna Maier Why use Google Apps Script? Custom SERP Analysis Situation SERP layouts are constantly changing. To help topic prioritisation, you want to scrape SERP data incl. all its layout components. Approach Use the DataForSEO SERP API to scrape SERP info & to calculate custom metrics like pixel rank. Script Example Google Sheet: bit.ly/dept-pixel-rank Campixx Talk: https://speakerdeck.com/johannamaier/ pixel-rank-the-better-metric-for-your-ranking Johanna Maier USE CASE #WTSFest
30. 30 #WTSFest Keyword List Device & API Credentials Script Menu Region, Language & Domain Custom SERP Analysis with DataForSEO
31. 31 #WTSFest Keyword List SERP Items Custom SERP Analysis with DataForSEO
32. 32 #WTSFest 1203 pixels 182 pixels 595 pixels
33. 33 #WTSFest Check your tool stack: Do you have access to APIs to speed up any workflows? Johanna Maier Why use Google Apps Script?
34. 34 #WTSFest 34 Johanna Maier Why use Google Apps Script? Situation Formulas (or custom functions) are triggered every time a spreadsheets loads. Long loading times and unwanted credit consumption from API calls. Approach (1) Use a custom menu or a time-based trigger to control when a function is run. (2) Use script to turn formulas into text and vice versa. Run via custom menu. Script Examples bit.ly/dept-wtsfest-berlin Johanna Maier On-Demand Execution USE CASE status-codes status-codes.gs
35. 35 #WTSFest Execute Functions & Formulas On-Demand status-codes status-codes.gs
36. 36 #WTSFest Execute Functions & Formulas On-Demand status-codes status-codes.gs
37. 37 #WTSFest 37 #WTSFest Apps Script Environment & Functions Apps Script Environment & Functions 02 Johanna Maier
38. 38 #WTSFest Access to the Apps Script environment
39. 39 #WTSFest Access to the Apps Script environment script.google.com > New project script.new
40. 40 #WTSFest All options open up the integrated code editor. ✍
41. 41 #WTSFest This is its own 'IDE', meaning 'Integrated Development Environment'.
42. 42 #WTSFest 42 Johanna Maier Apps Script Environment & Functions Integrated Development Environment Johanna Maier CONCEPT 'An IDE is a robust tool for writing, editing, debugging, and running your code. A text editor only lets you write and edit your code. You might have to step out of a text editor to run your code or download plugins to help it do the running for you.' Source: freeCodeCamp IDE: You can actually run your code & get a result. Both are ‘just’ code editors.
43. 43 #WTSFest Sections that you need to get started. Code window for Google Apps Script (syntax = JavaScript) Program controls Code files
44. 44 #WTSFest But there are also advanced options. Advanced options
45. 45 #WTSFest But there are also advanced options. Triggers: Schedule your code to run automatically. Executions: Logs every time any of your code was executed.
46. 46 #WTSFest Let’s start with the core element of each script: a function. Johanna Maier Apps Script Environment & Functions
47. 47 #WTSFest Think of it like a formula where you get to look inside how the formula magic works. Johanna Maier Apps Script Environment & Functions
48. 48 #WTSFest Like Google Sheets formulas, Apps Script functions are used to contain logic and to make it reusable. Johanna Maier Apps Script Environment & Functions
49. 49 #WTSFest 1 ✍ - Code.gs How to run your first function in Apps Script?
50. 50 #WTSFest 👆 2 How to run your first function in Apps Script? - Code.gs
51. 51 #WTSFest 3 How to run your first function in Apps Script? 👆 - Code.gs
52. 52 #WTSFest 👆 4 How to run your first function in Apps Script? - Code.gs
53. 53 #WTSFest 👆 5 How to run your first function in Apps Script? - Code.gs
54. 54 #WTSFest Sidenote: You will be asked for authorization. 🔗 more info 🔗 more info
55. 55 #WTSFest How to run your first function in Apps Script? 5 👀 Thanks to → console.log() 🤩 5 👆 - Code.gs
56. 56 #WTSFest 👆 5 5 👀 Logger.log() → does the same - Code.gs How to run your first function in Apps Script?
57. 57 #WTSFest 57 Johanna Maier Apps Script Environment & Functions JavaScript Functions Johanna Maier CONCEPT 'A function is a block of code that performs a specific task. JavaScript functions are basically used to encapsulate logic, making that code more reusable and easier to understand. Functions can take input in the form of parameters and can return a value or output.' Source: freeCodeCamp We reused myFirstFunction inside mySecondFunction ♻ 👀
58. 58 #WTSFest We can do more than just logging with our function outputs. Johanna Maier Apps Script Environment & Functions
59. 59 #WTSFest 59 Johanna Maier Apps Script Environment & Functions Run & Display Options Johanna Maier CONCEPT There are different ways to run the logic in a function and to display its output. Run it: via (1) the editor, (2) a button, (3) a custom menu or (4) triggers. Display it: with (1) the execution log, (2) UI alerts, (3) by writing to a sheet or (4) returning a value in a cell. RUN IT DISPLAY IT
60. 60 #WTSFest Don’t just log - ALERT! alert-write alert-write.gs
61. 61 #WTSFest Don’t just alert - WRITE! alert-write alert-write.gs
62. 62 #WTSFest Create MENUS to start it all! - custom-menu.gs
63. 63 #WTSFest Run everything from a MENU in the Sheets UI! - custom-menu.gs
64. 64 #WTSFest Besides your own menus, you can also use BUTTONS. alert-write custom-menu.gs
65. 65 #WTSFest Schedule your functions with TRIGGERS!
66. 66 #WTSFest Schedule your functions with TRIGGERS!
67. 67 #WTSFest JavaScript functions are like Google Sheets formulas. Johanna Maier Apps Script Environment & Functions
68. 68 #WTSFest Make your own formulas: custom functions
69. 69 #WTSFest Make your own formulas with custom functions. No logging, no alerting, no appending – just return 🤔 custom-functions custom-functions.gs
70. 70 #WTSFest Make your own formulas: custom functions custom-functions custom-functions.gs
71. 71 #WTSFest JavaScript & Google Sheets syntax has similar logic. Johanna Maier Apps Script Environment & Functions
72. 72 #WTSFest 72 #WTSFest Control Flow, Debugging & Prompting 03 Control Flow, Debugging & Prompting Johanna Maier
73. 73 #WTSFest ChatGPT will speed up your coding. But to fact-check it, it helps to think a bit like a machine. Johanna Maier Control Flow, Debugging & Prompting
74. 74 #WTSFest How does your computer try to run the code that ChatGPT gives you? Johanna Maier Control Flow, Debugging & Prompting
75. 75 #WTSFest 75 Johanna Maier Control Flow, Debugging & Prompting Control Flow in JavaScript Johanna Maier CONCEPT 'Control flow in JavaScript is how your computer runs code from top to bottom. It starts from the first line and ends at the last line, unless it hits any statement that changes the control flow of the program such as loops, conditionals, or functions.' Source: medium.com Source: Learning JavaScript, 3rd Edition
76. 76 #WTSFest 76 #WTSFest control-flow control-flow.gs
77. 77 #WTSFest 77 #WTSFest 1 1 3 4 5 5 5 5 5 6 3 4 1 2 3 4 5 6 5 6 2 2 2 2
78. 78 #WTSFest 78 #WTSFest 1 1 3 4 5 5 5 5 5 6 3 4 1 2 3 4 5 6 5 6 2 2 2 2
79. 79 #WTSFest 79 #WTSFest 1 1 3 4 5 5 5 5 5 6 3 4 1 2 3 4 5 6 5 6 2 2 2 2 💻🧠
80. 80 #WTSFest 80 #WTSFest 1 3 4 5 5 5 5 5 6 2 💻🧠 sheet: data object of tab 'control-flow'
81. 81 #WTSFest 81 #WTSFest 1 1 3 4 5 5 5 5 5 6 2 💻🧠 sheet: data object of tab 'control-flow' startText: 'Hello from the start!'
82. 82 #WTSFest 82 #WTSFest 1 1 1
83. 83 #WTSFest 83 #WTSFest 2 2 💻🧠 sheet: data object of tab 'control-flow' startText: 'Hello from the start!' resultSeparateFunction: 'Hello from the separate function!'
84. 84 #WTSFest 84 #WTSFest 2 2 2 2
85. 85 #WTSFest 85 #WTSFest 1 3 4 5 5 5 5 5 6 3 2 💻🧠 sheet: data object of tab 'control-flow' startText: 'Hello from the start!' resultSeparateFunction: 'Hello from the separate function!' condition: true conditionState: 'The condition is: true’
86. 86 #WTSFest 86 #WTSFest 3 3 3
87. 87 #WTSFest 87 #WTSFest 1 3 4 5 5 5 5 5 6 2 💻🧠 sheet: data object of tab 'control-flow' startText: 'Hello from the start!' resultSeparateFunction: 'Hello from the separate function!' condition: true conditionState: 'The condition is: true’ 'if statement' 4
88. 88 #WTSFest 88 #WTSFest 4 4 4 'if statement'
89. 89 #WTSFest 89 #WTSFest 1 3 4 5 5 5 5 5 6 2 💻🧠 sheet: data object of tab 'control-flow' startText: 'Hello from the start!' resultSeparateFunction: 'Hello from the separate function!' condition: true conditionState: 'The condition is: true’ iterator: 1 loopText: ‘Help, I’m in loop N° 1 5 ‘while loop’
90. 90 #WTSFest 90 #WTSFest 5 5 5 ‘while loop’
91. 91 #WTSFest 91 #WTSFest 1 3 4 5 5 5 5 5 6 2 💻🧠 sheet: data object of tab 'control-flow' startText: 'Hello from the start!' resultSeparateFunction: 'Hello from the separate function!' condition: true conditionState: 'The condition is: true’ iterator: 1 loopText: ‘Help, I’m in loop N° 1 iterator: 2 loopText: ‘Help, I’m in loop N° 2 (…) loopText: ‘Help, I’m in loop N° 5 iterator: 6 5 ‘while loop’
92. 92 #WTSFest 92 #WTSFest 5 5 5 ‘while loop’ 5 5
93. 93 #WTSFest 93 #WTSFest 1 3 4 5 5 5 5 5 6 6 2 💻🧠 sheet: data object of tab 'control-flow' startText: 'Hello from the start!' resultSeparateFunction: 'Hello from the separate function!' condition: true conditionState: 'The condition is: true’ iterator: 1 loopText: ‘Help, I’m in loop N° 1 iterator: 2 loopText: ‘Help, I’m in loop N° 2 (…) loopText: ‘Help, I’m in loop N° 5 iterator: 6 endText: ‘Hello from the end!’
94. 94 #WTSFest 94 #WTSFest 6 6 6
95. 95 #WTSFest 95 #WTSFest 1 1 3 4 5 5 5 5 5 6 3 4 1 2 3 4 5 6 5 6 2 2 2 2
96. 96 #WTSFest 96 #WTSFest 1 1 3 4 5 5 5 5 5 6 3 4 1 2 3 4 5 6 5 6 2 2 2 2 Source: Happy Duck ! : r/duck
97. 97 #WTSFest 97 #WTSFest 1 1 3 4 5 5 5 5 5 6 3 4 1 2 3 4 5 6 5 6 2 2 2 2
98. 98 #WTSFest 98 #WTSFest 1 1 3 4 5 5 5 5 5 6 3 4 1 2 3 4 5 6 5 6 2 2 2 2
99. 99 #WTSFest 99 #WTSFest 1 1 3 4 5 5 5 5 5 6 3 4 1 2 3 4 5 6 5 6 2 2 2 2
100. 100 #WTSFest 100 Johanna Maier Control Flow, Debugging & Prompting Rubber Duck Debugging Johanna Maier CONCEPT 'method of debugging code by articulating a problem in spoken or written natural language' Source: Wikipedia Rubber Duck Debugging
101. 101 #WTSFest 101 Johanna Maier Control Flow, Debugging & Prompting Debugging in the Google Apps Script IDE Johanna Maier CONCEPT Logging & execution log: Use console.log() to verify that your variables have the expected values & data types at specific steps in your control flow. Debugger & breakpoints: Use the built-in debugger with breakpoints to look inside the machine’s brain at specific moments.
102. 102 #WTSFest 102 #WTSFest 1 👆 Set 'breakpoints' at the code line where you want to stop for inspection. Resume code execution. Inspect the current state of variables at this point in the code. 4 💻🧠 3 👆 2
103. 103 #WTSFest Always have the control flow in mind when working with ChatGPT. Johanna Maier Control Flow, Debugging & Prompting
104. 104 #WTSFest Example prompt structure in ChatGPT “Write a Google Apps Script that does [...] & uses [...] as input. It gets the input from [...]. The input should be transformed like [...]. The output should be written to [...]. Here is an example of the input & output: [...].”
105. 105 #WTSFest Example prompt for the SurferSEO API script. Johanna Maier Control Flow, Debugging & Prompting surfer-seo-briefings surfer-seo-briefings.gs
106. 106 #WTSFest Johanna Maier Control Flow, Debugging & Prompting I want to write a Google Apps Script that sends specific keyword data to an API and returns a URL where we can look at the result. I need to get the keyword data from within a sheet. The keywords for one request are in the same row but different columns: E, F, G, H, I - It's possible that the cells are empty and contain no keywords. The rows that need to be sent to the API are marked in column A with "2 - SurferSEO API | Create Content Briefing". For each row, I want to send the following request format to the specified API endpoint. curl -X POST 'https://app.surferseo.com/api/v1/content_editors' -H 'API-KEY: <your key>' -H 'Content-Type: application/json' --data-raw '{ "keywords": ["seo research","seo article optimization"], "location": "United States" }' The response looks like this: { "state": "scheduled", "permalink_hash": "kKi7n3pkRk7Gw5cxKDiBAbCAybnDTt2z", "id": 5632898 } Save the “permalink_hash” value of the response in column C and the “id” in column D of the processed row. examples from SurferSEO docs
107. 107 #WTSFest Knowing basic JavaScript syntax helps you to accelerate with ChatGPT. Johanna Maier Control Flow, Debugging & Prompting
108. 108 #WTSFest SEO & Google Sheets are a great bridge into the world of coding. Johanna Maier Control Flow, Debugging & Prompting
109. 109 #WTSFest Thank you! Johanna Maier Control Flow, Debugging & Prompting Appendix: Checklists & Resources Google Sheets script examples: bit.ly/dept-wtsfest-berlin References to sheet: Apps Script article: blog 👀 tab-name script-name.gs
110. 110 #WTSFest 110 #WTSFest Appendix: Checklists & Resources Appendix: Checklists & Resources 04 Johanna Maier
111. 111 #WTSFest 111 Johanna Maier Checklists & Resources When to use Apps Script to automate your task? ❏ Is it a repetitive task? ❏ Can you write down every step of it? ❏ Do you have programmatic access to the data sources? (e.g. API credentials) ❏ Is there a reason why a simple formula cannot solve the issue? Some examples: ❏ Long loading times of formulas ❏ Complex nesting of formulas ❏ Advanced logic like API calls ❏ Need to control execution times (e.g. scheduling via triggers) Johanna Maier CHECKLIST
112. 112 #WTSFest 112 Johanna Maier Checklists & Resources How to get started with Google Apps Script? ❏ Get familiar with advanced Google Sheets formulas as much as possible. ❏ Learn the basic Apps Script syntax: ❏ Check the resources on the next slide. ❏ But don't get lost in too many tutorials - try to solve your own problems right away! ❏ Find a use case where automation makes sense. ❏ Ask ChatGPT to write simple scripts and test them in the code editor (see prompting checklist). ❏ Try to understand how each step of the syntax works. ❏ Ask ChatGPT to clarify all coding concepts that are new to you in simple terms. ❏ It’s not working? (see debugging checklist) Johanna Maier CHECKLIST
113. 113 #WTSFest 113 #WTSFest 113 Master Google Sheets Options & Formulas Learn Coding using Google Sheets and Apps Script Apps Script Beginner Guides by Ben Collins Official Google Ressources & Code Labs Intro to JavaScript on FreeCodeCamp Hannah Rampton @ WTSFest 20 & Advanced Formulas 30 Day Challenge (Ben Collins) Apps Script for SEOs by David Sottimano https://spreadsheet.dev/learn-co ding-google-sheets-apps-script Google Apps Script: A Beginner's Guide & Introduction To Google Apps Script Apps Script fundamentals codelabs overview & Hands-on with Google Apps Script Basic JavaScript & Debugging & Basic Data Structures (everything else not needed at first) TechSEO Boost - Apps script for SEOs (note: examples in old Apps Script IDE before 2021) Great SEO Use Cases Built with Google Apps Script Agency Automators with Advanced SEO Use Cases Latest News & Guides on Google Apps Script https://keywordsinsheets.com/fre e-sheets-scripts/ https://www.youtube.com/watch ?v=aSOxOTG44Vo&t=2328s https://pulse.appsscript.info/ Resource Overview: Learn the Basics 🤓
114. 114 #WTSFest 114 Johanna Maier Checklists & Resources JavaScript Variables Johanna Maier CONCEPT JavaScript variables are like cells in Sheets. Both store a value of a specific data type and make it referenceable. In JS, you create and name a new variable with 'var [name]' and assign it a value with '='. To reference it later, you can just use its name. In Google Sheets you simple write the value into a cell and reference it. Google Sheets Cell JavaScript Variable basic-js-syntax.gs
115. 115 #WTSFest 115 Johanna Maier Checklists & Resources JavaScript Data Types Johanna Maier CONCEPT Data types define the nature of a value in a JS variable or a Google Sheets cell. They influence the operations that you can do with a value, e.g. you can 'sum up' numbers but not strings. Use the ‘typeof’ operator to check the data type of a value in JavaScript, e.g. console.log(typeof event); outputs ‘string’. In Google Sheets, you can use formulas like TYPE(), ISNUMBER() or ISTEXT(). Google Sheets JavaScript basic-js-syntax.gs
116. 116 #WTSFest JavaScript data types compared to Google Sheets. • Strings & numbers are single values written in cell. • Booleans are true or false - like checkboxes. • Arrays are like a list of values, like in a column. • Objects contain a list of key-value pairs. The structure is also used in JSON-LD syntax that we know from structured data.
117. 117 #WTSFest 117 Johanna Maier Checklists & Resources JavaScript Arrays Johanna Maier CONCEPT Arrays are like a list of values, comparable to a column or a row in Google Sheets. A typical App Script use case: Grabbing & storing data from your sheet to transform it. To do so, you have to access the array values. How to access arrays? You can use an index, similar to the INDEX() formula in Sheets. Each value in a JS array has an index - starting at ‘0’. To access a specific array value use arrayName[index]. Google Sheets JavaScript basic-js-syntax.gs
118. 118 #WTSFest 118 Johanna Maier Checklists & Resources JavaScript Objects Johanna Maier Objects contain a list of key-value pairs, similar to two connected columns in Google Sheets. The JavaScript object structure is also used in the JSON-LD syntax like for structured data. A typical App Script use case: Tool APIs send us data in JSON syntax and we want to grab the information relevant to use. How to access objects? Comparable to the VLOOKUP() formula, we can use the key name, e.g. objectName[keyName]. CONCEPT Google Sheets JavaScript basic-js-syntax.gs
119. 119 #WTSFest 119 Johanna Maier JavaScript Functions Johanna Maier CONCEPT 'A function is a block of code that performs a specific task. JavaScript functions are basically used to encapsulate logic, making that code more reusable and easier to understand. Functions can take input in the form of parameters and can return a value or output.' Source: freeCodeCamp We reused myFirstFunction inside mySecondFunction ♻ 👀 Checklists & Resources
120. 120 #WTSFest 120 Johanna Maier JavaScript Methods Johanna Maier CONCEPT 'Methods are functions that are associated with a particular object. They are used to perform a specific task on the object.' The key: All variables that we define are some kind of object - so you can use methods on them like this: objectName.methodName() Sources: playcode.io & dev.to toUpperCase() is a JavaScript method that you can use on a String object like the variable ‘event’ in the example above (see docs). The method’s specific task is to take every character and replace it with the capitalised version. It can be compared to the UPPER() formula in Sheets. Checklists & Resources
121. 121 #WTSFest 121 #WTSFest 121 JavaScript Methods Debugger Triggers Executions Calling External APIs Pre-defined functions that you can use on specific objects (e.g. a string or an array). JavaScript docs & Apps Script docs Version Control in Github Use the built-in debugger with breakpoints to assess the state of each variable at specific moments. Apps Script docs Execute your code automatically based on an event in the spreadsheet (‘on open’) or at a specific time (‘daily between 9-10 am’). Apps Script docs & tutorial In the IDE, check the 'Executions' menu icon to see the code that was executed in this specific project. The Apps Script Dashboard collects this info from all projects that belong to your account. You can use the UrlFetchApp.fetch(), HTTPResponse.getContentText() & JSON.parse() to make API requests & parse the JSON output to a data object. Apps Script Docs & tutorial If you want to develop locally and store your code in Github, use the open-source tool ‘clasp’. Clasp Docs & Version Docs HTML for Front-ends & Emails Version Control in Deployments Libraries & Services You can write HTML files in the IDE to prepare email templates or even to host proper application frontends. Web App Example - Email Example Deploy your changes to use the built-in version history. Use deploy as ‘library’ to save your script progress. Example You can also deploy your code as official, public add-on or library. Use open source libraries written by other users (library database) and built-in Google services (docs) to speed up creating your logic. Resource Overview: Advanced Features 🚀
122. 122 #WTSFest 122 Johanna Maier Checklists & Resources How to prompt ChatGPT for scripting? ❏ Remember the control flow (🦆) & list the main steps the script has to do in plain language. ❏ Specifically list the functional requirements: ❏ Language (Google Apps Script) ❏ Input/output parameters (e.g. “data from tab X, row Y, column Z”) ❏ If possible, give concrete examples of input/output (e.g. from API docs). ❏ Do you want to turn a formula into a script? Give the formula as input. ❏ No sensitive data like API keys. ❏ For large scripts: Split the prompt up, test and debug right away. ❏ If you know them, use syntax terms like ‘function’, ‘variable’, ‘return’, ‘condition’, … Johanna Maier CHECKLIST
123. 123 #WTSFest 123 Johanna Maier Checklists & Resources How to debug in Google Apps Script? ❏ Read the error: often it already gives you hints like code lines or missing credentials. ❏ Give the error & script to ChatGPT (no sensitive data!). ❏ Use the debugger (break points) & execution log (console.log). ❏ Use the rubber duck approach and think through the steps of the control flow. ❏ Use typeof to check variable data types. Are you writing a script from scratch? ❏ Debug during writing - console.log() intermediate values at every step. Does your script come from ChatGPT? ❏ Check if you still need to fill in placeholder info like API keys or sheet names/IDs. ❏ Split the prompt up, test single parts and debug right away, before you proceed. Johanna Maier CHECKLIST