Google Sheets Automation with Macros Questions and Answers 1 — Questions and Answers
Question 1: 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?
- Only the originally recorded cell will be formatted.
- The formatting will be applied to cell C5 first, and then to cell D10. (Correct answer)
- The macro will format only cell C5 each time it is run.
- An error will occur because the macro was not recorded for cell D10.
Correct 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.
Question 2: When you record a macro in Google Sheets, what is automatically generated in the background?
- A new, separate Google Sheet file containing the macro steps.
- A private folder in Google Drive to store the macro.
- A Google Apps Script function that replicates the recorded actions. (Correct answer)
- An XML file embedded within the sheet's metadata.
Correct 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.
Question 3: 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?
- Use the 'Insert > Button' menu and select the macro from a dropdown list.
- Format a cell to look like a button and use conditional formatting to link the macro.
- Create a custom formula =RUNMACRO("ArchiveData") in a cell.
- Insert a Drawing, style it as a button, and then use the 'Assign script' option to link the macro's function name. (Correct answer)
Correct 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').
Question 4: Which of the following is a key limitation of the macro recorder in Google Sheets?
- Macros cannot be edited once they are recorded.
- Macros recorded in one Google Sheet cannot be directly used in a different Google Sheet without manual steps. (Correct answer)
- Macros can only be activated by keyboard shortcuts, not from the menu.
- The macro recorder cannot record formatting changes like changing a cell's color.
Correct 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.
Question 5: 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?
- The macro will automatically detect and sort the new range A2:D150.
- The macro will only sort the original range, A2:D100, ignoring the new data. (Correct answer)
- The macro will produce an error because the data range has changed.
- The macro will ask the user to confirm the new range before sorting.
Correct 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.
Question 6: To make a macro run automatically every time a user changes the value of any cell in the spreadsheet, what must be done?
- Assign the macro to the special 'onOpen(e)' simple trigger.
- Create a time-driven trigger set to run every minute.
- The macro function must be manually renamed to 'onEdit(e)' in the Apps Script editor. (Correct answer)
- This is not possible; macros can only be run manually via the menu or shortcuts.
Correct 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.
Google Sheets Automation with Macros Questions and Answers 1
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?