Google Sheets Automation with Macros Flashcards

An analyst records a macro to apply specific formatting (bold font, yellow background) to a selected cell. They choose the 'Use relative references' option during recording. If they select cell C5 and run the macro, and then later select cell D10 and run the same macro, what will be the result?

Answer: The formatting will be applied to cell C5 first, and then to cell D10.

When a macro is recorded with 'Use relative references', it performs the recorded actions on the currently selected cell or range, not on the specific cell where the actions were originally recorded. Therefore, the formatting will be applied to whichever cell is selected when the macro is executed.

When you record a macro in Google Sheets, what is automatically generated in the background?

Answer: A Google Apps Script function that replicates the recorded actions.

Recording a macro in Google Sheets automatically creates an Apps Script function. This function contains the JavaScript code that corresponds to the series of user interface interactions you recorded. This script is stored in a file named `macros.gs` bound to the spreadsheet.

A user wants to create a clickable button on their sheet that executes a macro named 'ArchiveData'. Which of the following describes the correct procedure?

Answer: Insert a Drawing, style it as a button, and then use the 'Assign script' option to link the macro's function name.

To create a clickable button that runs a script or macro, you must first insert an object like an image or a drawing. After creating the drawing and styling it, you can click the three-dot menu on the object and select 'Assign script', where you will enter the exact name of the macro function ('ArchiveData').

Which of the following is a key limitation of the macro recorder in Google Sheets?

Answer: Macros recorded in one Google Sheet cannot be directly used in a different Google Sheet without manual steps.

Macros are bound to the specific Google Sheet in which they are created. To use a macro in a different sheet, you must manually copy the Apps Script code from the source sheet's script editor and paste it into the destination sheet's script editor.

A user records a macro with 'Use absolute references' selected. The macro was recorded to sort the specific range A2:D100. If the user later adds more data and wants to sort the range A2:D150, what will happen when they run the macro?

Answer: The macro will only sort the original range, A2:D100, ignoring the new data.

When 'Use absolute references' is selected, the macro records the exact cells and ranges involved. It will always perform the action on that specific, hardcoded range (A2:D100). To include the new data, the user would need to edit the underlying Apps Script to change the range.

To make a macro run automatically every time a user changes the value of any cell in the spreadsheet, what must be done?

Answer: The macro function must be manually renamed to 'onEdit(e)' in the Apps Script editor.

Google Apps Script has special reserved function names called simple triggers. The 'onEdit(e)' trigger is specifically designed to execute automatically whenever a user modifies a cell's value. To use this, the function generated by the macro recorder would need to be renamed to 'onEdit' or called from within an 'onEdit(e)' function in the script editor.