Part 00 Getting Started with Google Apps Script

Project 00: What is Google Apps Script?
_Types of Apps Scripts
_Create an Apps Script project and name the file

Project 01: Creating and Running an Apps Script Function
_ Accessing documents using classes

Part 01: Basic Apps Script Exercises

Project 02: Opening a Spreadsheet and Reading Values
_ Setting up basic project practice
_ Retrieving data from a specific cell
_ Apps scripts can be executed by specifying a function
_ Get specific range data
_ [with ChatGPT] Shall we get the unit price and quantity from pears to kiwis?
_ Get all data from a sheet whose size changes
_ Retrieving data by range name
_ [with ChatGPT] How about asking to specify the range by row?

Project 03: Entering Values ​​into a Spreadsheet
_ Entering data into cells
Enter data in the _ row
_ [with ChatGPT] If you encounter an error, ask ChatGPT.
_ Entering data in multiple rows

Project 04: Open a Google Doc and Enter Values
_ Read all text from Google Docs
_ Adding and formatting text in Google Docs

Project 05: Creating a Multiplication Table
_ Generate a multiplication table in a row
_ Create a multiplication table in table format

Project 06: Creating Your Own Menu in a Spreadsheet
_ Create your own menu
_ [with ChatGPT] Shall we ask them to implement the menu?

Project 07: Creating Your Own Button in a Spreadsheet
_ Create a button
_ Write addition and subtraction scripts to connect to buttons
_ Attaching a script to a button

Project 08: Executing functions at specific times and events using triggers
_ Apps Script provides built-in triggers and external triggers.
_ Using an external trigger
_ Delete trigger

Project 09: Easily Transfer Data from Spreadsheets to Google Docs
_ Importing data from another spreadsheet
_ Create a table by copying spreadsheet data to Google Docs
_ [with ChatGPT] Let's ask ChatGPT to move data from a spreadsheet to a Google Doc.

Project 10: Creating Checkboxes in a Spreadsheet
_ Create a checkbox and count the number
_ Check or uncheck the checkbox
_ Applying an event to a checkbox

Project 11: Creating a Spreadsheet Dropdown
_ Creating a dropdown using an array
_ Create a dropdown with a specific range
_ Create a dropdown to select the year
_ Create a dropdown to select a month

Project 12: Calculating Spreadsheet Values ​​Your Way
_ [with ChatGPT] Request training data from ChatGPT
_ Calculate directly in Apps Script and enter values ​​into cells
_ Applying calculation formulas to cells using R1C1

Part 02 Upgrading Your Work with Apps Script

Project 13: Placing Announcements in Documents
_ Control the UI by displaying notifications, confirmations, prompts, etc.
_ [with ChatGPT] Should I ask them to implement a sidebar?
_ Displaying a notice by combining triggers and UI methods

Project 14: Retrieving Information About Users Accessing Documents
_ Retrieving user information with the Session class
_ Retrieving user information with Drive API
_ How about displaying the username and email address in a modal?

Project 15: Creating a Data Sort Button in a Spreadsheet
_ Writing ascending and descending functions
_ [with ChatGPT] Let's create a button to sort unit prices.
_ Using Google Drive

Project 16: Creating a Table of Contents in a Spreadsheet
_ Get sheet name and ID
_ Create a list of sheet names and sheet URLs
_ Create a table of contents sheet and list the sheets
_ [with ChatGPT] Let's neatly express URLs with hyperlinks.

Project 17: Upgrading Find and Replace in Google Docs
_ Extract the text and check it
_ Change all text in a document
_ Let's find the word and change its format.

Project 18: Formatting Phone Numbers with Regular Expressions
_ What is a regular expression?
_ Change phone number format
_ [with ChatGPT] Shall we reverse the phone number pattern again?
_ Changing phone number format while inputting with onEdit() trigger

Project 19: Replace Text in Multiple Google Docs in Drive at Once
_ Access Google Drive and get file information
_ Change text in multiple Google Docs files in Drive
_ Sync with Google Calendar

Project 20: Managing Google Calendar Schedules
_ Register your schedule
_ Check the schedule
_ Delete schedule
_ Linking Spreadsheets and Google Calendar

Project 21: Leave an Author Log When Editing Data in a Spreadsheet
_ Automatically enter data modification information
_ [with ChatGPT] How to leave previously entered values?

Project 22: Managing Google Drive Files
_ Get file information
_ [with ChatGPT] Let's get file information using the file ID?

Project 23: Creating a Backup File in Google Drive
_ Create a backup file
_ Delete backup files
_ Create backup files at specific times with triggers

Part 03: Using Spreadsheets Properly

Project 24: Shading and Bordering
_ Apply shading
_ Applying cross colors
_ Apply border

Project 25: Formatting Data
_ Applying number format
_ [with ChatGPT] What if you want to add a circle to the thousands comma?
_ Applying different formats to specific ranges
_ Change color based on a specific value
_ Apply date format

Project 26: Creating Conditional Formatting
_ Creating conditional formatting
_ [with ChatGPT] How to apply conditional rules only to those that include 'collision' in the accident type classification?

Project 27: Creating a Filter
_ Preparing data in the public data portal
_ Create a filter
_ Apply filter
_ Applying two or more filters
_ [with ChatGPT] Would you like to ask ChatGPT for a filter that meets your conditions?
_ Save filtered data as a new sheet

Project 28: Merging Cells with Consecutive Values
_ Merge cells in only one column
_ Merge multiple columns

Project 29: Creating a Chart in a Spreadsheet
_ How to use the newChart() method to create a chart
_ setOption() method to set chart options
_ Creating a line chart from spreadsheet data
_ Create charts of specific ranges apart

Project 30: Creating a Dependent Dropdown
_ Creating a trial-dependent dropdown
_ Create a branch-dependent dropdown based on the selection of the attempt
_ Processing notifications for cells being processed

Project 31: Working on Multiple Spreadsheet Files at Once
_ Iterating with ID
_ Repeating operations on multiple spreadsheet files in a folder
_ [with ChatGPT] Let's assign styles to multiple spreadsheets at once.

Part 04 Using APIs with Apps Script

Project 32: Retrieving Housing Subscription Information from the Public Data Portal Using Apps Script
_ Understanding and testing API calls in the public data portal
_ Calling APIs from Apps Script
_ Processing API call results in JSON format
_ Let's learn how to process XML data.

Project 33: Sending KakaoTalk Messages with Apps Script
_ A quick look at the process for sending KakaoTalk messages
_ Registering a Kakao Developer Application
_ Create an authorization code issuance URL
_ Get an authorization code
_ Generating access tokens and refresh tokens
_ Send me a message
_ Automatically renew tokens

Project 34: Sending Text Messages with Apps Script
_ Obtaining SMS transmission service API authentication key
_ Send a text message
Project 35: Sending Slack Bot Messages
_ Setting up the Slack API
_ Send a Slack message

Part 05: Applying AppScript to Real-World Work

Project 36: Using ChatGPT with Apps Script
_ Obtaining a ChatGPT API authentication key
_ Using ChatGPT

Project 37: Sending Text Messages to Customers Selected from a Spreadsheet
_ Please prepare
_ Send text messages to selected customers
_ Dynamically retrieve and use the required content

Project 38: Sending an Email Attachment to Customers Selected from a Spreadsheet
_ Please prepare
_ Send an email with attachments to selected customers

Project 39: Receive stock price information after the domestic market closes via KakaoTalk
_ Please prepare
_ Import Naver Securities page data
_ Send stock price information as a KakaoTalk feed message
_ Add a page navigation button

Project 40: Turning a Spreadsheet into a Database Web Page
_ Please prepare
_ Using the built-in trigger doGet() method
_ Deploying Apps Script Code

Project 41: Automating a Reservation System with Google Services
_ Please prepare
_ Configuring the reservation screen
_ Connecting Google services

Project 42: Creating Response Templates with ChatGPT
_ Please prepare
_ Declare variables and functions required to use the API
_ Create a reply with ChatGPT and send a text message

Project 43: Analyzing Spreadsheet Data with ChatGPT
_ Please prepare
_ Create an analyzer sidebar that lets you select sheets
_ [with ChatGPT] Let's create a code to convert data into CSV format.
_ Generating responses with the ChatGPT API

Project 44: Creating a Spreadsheet-Based Product Management System
_ Please prepare
_ Examining the product registration function
_ Examining the product search function
_ Examining the product deletion function

Let's study together in the open KakaoTalk room and Naver Cafe that are 500% effective.

By gathering together to study, discuss, network, and read the entire book, we can grow stronger.
Study and research AppScript task automation together with the author.
- Open KakaoTalk: open.kakao.com/o/gBWRpyvg
- Naver Cafe: cafe.naver.com/gpto

I study difficult content again through YouTube lectures.

We provide YouTube lectures on only the most essential projects.
Watch videos to learn about parts that were difficult to understand in text, or parts that you understood in text but need further explanation.
- YouTube Channel: youtube.com/@editorp89

We provide all 45 crazy automation example codes.

While I recommend learning by following the code provided in the book, for those who are too busy, want to learn faster, or want to start writing right away, I've provided sample code that you can copy and paste right away.
- Download the practice file: vo.la/owKJxg

★ We teach you how to catch fish and give you fish too!
★ Detailed practical training course explanation, from the basics of business automation to how to connect to Kakao
★ 45 crazy examples you can use right away, including spreadsheets, group texts, and Slack notification bots.

Learn how to catch the fish called "efficiency" in the vast and useful sea of ​​business automation.
This book is structured so that anyone can follow along, from the basics of task automation to advanced applications like connecting to external data, with step-by-step practice.
After reading the book, you will find not only practical examples but also ways to automate your own applications.

We also offer ready-to-eat fish.
All the code in the book is available online for you to copy and paste.
The exercises you learned in 'Chapter 5: Applying AppScript to Real-World Work' are provided as completed templates, so you can use them right away in the tasks you need.


★ A spoonful of the author's work automation know-how, which built an in-house system through work automation.
★ Two spoonfuls of error points frequently encountered by the editor while practicing with the reader's eyes
★ We guide you to learn and apply it in practice more easily and reliably!

This book generously shares the author's extensive automation experience and know-how.
We cover practical task automation that anyone can do, from asking ChatGPT about task automation to connecting to Kakao.
Would you like to get a quick taste of what's included?

Crazy Usage 01: Want to find and underline specific words? Apply formatting in bulk.
Crazy Utilization 02: Run it at a specific time every day! Update data daily with a trigger.
Crazy Use 03: Having trouble unifying phone number formats? Change the automatically entered text format.
Crazy Utilization 04: Enter your project schedule all at once! Register your Google Calendar schedule all at once.
Crazy Utilization 05: Edit documents with a single click! Edit multiple files in a folder at once.
Crazy Utilization 06: Want to leverage public data? Get real estate information using public APIs.
Crazy Use 07: Hire AI as a Customer Service Representative! Receive ChatGPT responses and send customer response texts.
Crazy Use 08: Let AI analyze your data! Analyze spreadsheet data with ChatGPT.
Crazy Use 09: You can use it even outside of Google! Create a stock price alert bot with KakaoTalk.

This book is not just a simple book on business automation, but a practical guide to business automation that can be applied immediately in practice.
The editor personally followed all 45 exercises and noted common errors and precautions, so please take a look.
As you follow along with the book, I hope you'll come up with your own ideas for automating your work, thinking, "This was inconvenient, but I can solve it like this!"

★ Learn, Apply, and Solve Problems
★ Learn more effectively with ChatGPT!

Readers who are new to code may vaguely think it is difficult.
But with ChatGPT, all these worries can be solved.


Through the 'with ChatGPT' corner, we explain how to create automation code with ChatGPT in between practice sessions.
By using what you've learned in the text to answer questions about changing the desired part and troubleshooting errors with ChatGPT, you too will quickly be able to write automation code with ChatGPT.
It also includes helpful prompting tips, such as what format to use when asking questions and what information to include.


★ Why should I automate with Google Apps Script?
★ Because it runs 24 hours a day, 365 days a year without a server!

If I want to automate with Python, I need to keep my PC on all the time or set up a server.
On the other hand, using Google Workspace means you can avoid such hassles.
Additionally, Google Workspace today offers a variety of tools, including Word, Spreadsheets (Excel), and Calendar, allowing for more powerful automation.
So, this book provides the ultimate in crazy automation using Google Workspace and Apps Script.

Here are three questions frequently asked by those who are still new to Google Apps Script!

〈One〉What is Google Apps Script?

Google Apps Script is a cloud-based scripting language that lets you connect and automate apps in Google Workspace.
It's JavaScript-based and lets you automate repetitive tasks or connect various Google apps like Google Docs, Sheets, and Drive to help you work more efficiently.
For example, you can easily set up tasks such as automatically sending emails on a schedule or processing data in a spreadsheet in real time.


〈2〉 Isn't Apps Script difficult to code?

Apps Script can be daunting at first.
But if you can do something that used to take three hours every day in just 10 minutes, isn't it worth learning? Once you get used to it, you'll quickly see recurring functions and patterns.
This book will help you learn AppsScript step by step, from basic functions to advanced content.
Especially when using Apps Script with ChatGPT, it is much easier to learn and use.
The "with ChatGPT" section of this book explains in detail how to apply AppsScript using ChatGPT, how to ask about unlearned code, how to resolve errors, and how to create dummy data for training.


〈Set〉 Isn't Apps Script only useful when writing spreadsheets?

As I mentioned in the first question, Apps Script can handle the entire Google Workspace.
You can automate a variety of tools, such as Google Docs, Drive, and Calendar.
Furthermore, by leveraging APIs, you can retrieve and manipulate data outside of Google.
This book contains 45 projects that demonstrate various uses.
Because we've covered a wide range of examples that will make you wonder, "Is this even possible with AppsScript?", even readers who know AppsScript but didn't know how to use it will find it helpful in gaining new ideas.