Google Sheets + AI Challenge, Session 2

Ohppudu Excel said it has revised its course structure and curriculum because of this.

Reviewed last week's material: auto-completing the amount-calculation formulas in an order sheet, using the paste-format shortcut (Ctrl+Alt+V), and assigning colors through data validation.

Explained that even without knowing the VLOOKUP function, you can pull product names using Google Sheets' auto-complete feature. Entering a few patterns lets Sheets build the function for you, and the formula display feature makes this automatic input method usable.

Setting the list range as an open range (for example, C2:C) lets the data update automatically and improves management efficiency.

Enabling multi-select in the validation rule settings lets you choose several items at once, which can be used with color coding to prevent missed tasks and build a management system.

Explained that using 'filter views' instead saves each person's view state independently, which prevents conflicts when working in a shared document.

Demonstrated how to create an automatic formatting rule that highlights the entire row of items meeting a condition (for example, the status 'Accepting'). Explained that a custom formula and the column-fixing symbol ($) let formatting apply to the whole row automatically whenever the condition is met, making key data easier to spot and improving work efficiency.

Noted that for simple tasks, asking AI and waiting for an answer every time can actually be less efficient. Stressed that understanding basic functions and the principles of automation matters more than relying heavily on AI, and that people should focus on building their own problem-solving skills.

Array formulas can be created easily with a shortcut (Ctrl+Shift+Enter), and setting an open range (for example, C3:C) makes the formula apply in real time as data is added, reducing repetitive formula entry.

Presented a way to fix the problem of array formulas showing 0 as the result. Combining the formula with an IF function so that a value of 0 is shown as blank prevents unnecessary zeros from appearing during data analysis and keeps the sheet cleaner.

This error can be fixed by deleting the values in that space so the array formula has room to spread its results.

Knowing the purpose makes a function easy to apply even without memorizing it, which reduces the learning burden. This approach focuses on building practical problem-solving skills.

Demonstrated how to pull values from several fields at once using VLOOKUP. Field numbers are entered as an array inside curly braces ({}) and combined with ARRAYFORMULA to output values from multiple columns simultaneously. This avoids rewriting complex formulas one by one and maximizes automation efficiency.

Using UNIQUE to extract only the unique values from a data range builds the customer list, which updates automatically as data is added and reduces management effort.

Rejecting input can restrict adding data, so setting a warning keeps data entry flexible while still making errors visible.

Presented a way to restrict input so that specific customer information cannot be changed, protecting data integrity. Using the data validation rule options to lock the list prevents unintended edits and incorrect entries, which helps keep important data accurate.

Instead of limiting the SUMIF condition to one cell (J2), it was set to look at the entire J column and applied as an array function by pressing Ctrl+Shift+Enter. This makes the report update automatically whenever the data changes.

Stressed that the QUERY function is very important in Google Sheets and is based on the database language (SQL). Explained that mastering it can make you an advanced Sheets user, and that you only need to know six core clauses (SELECT, WHERE, ORDER BY, LIMIT, GROUP BY, PIVOT). These functions make complex data analysis and report building efficient.

Showed a practical automation example: anyone can send automatic messages every minute for free.

Guided users through setting up the Kakao developer tools for automatic notifications. Explained the precautions for issuing and managing a REST API key from the platform key menu. Also covered in detail registering the redirect URL (localhost:3000) and configuring Kakao login and access permissions.

If an error occurs, users were advised to check the permission settings window again and retry.

Since one trigger run takes about 2 seconds, it can be used almost without limit for typical purposes, the instructor added. This emphasized that the time is sufficient for most automation tasks.

Stressed that to send notifications to others, you must register an Alimtalk template and send through a professional service provider. This clearly shows the limits of the free service.