Google Apps Script & Spreadsheet Automation

                         QUESTION PAPER

Google Apps Script & Spreadsheet Automation

Time: 2 Hours

Maximum Marks: 80

Instructions:

All questions are compulsory.

Read each question carefully before answering.

Write neat and clean answers.

_______________________________________________

SECTION A: MULTIPLE CHOICE QUESTIONS

                                            (10 × 1 = 10 Marks)

1. Which object is used to access the active spreadsheet in Google Apps Script?

   a) Sheet.getActiveSpreadsheet()

   b) SpreadsheetApp.getActiveSpreadsheet()

   c) Google.getActiveSpreadsheet()

   d) Apps.getActiveSpreadsheet()

2. The getSheetByName() method returns:

   a) Sheet object if found, null if not found

   b) Always returns a sheet object

   c) Returns an array of sheets

   d) Returns a string

3. Which method is used to remove all data from a sheet?

   a) delete()

   b) remove()

   c) clear()

   d) erase()

4. The VLOOKUP function syntax requires how many parameters?

   a) 2

   b) 3

   c) 4

   d) 5

5. What does the setFontWeight("bold") method do?

   a) Changes font size

   b) Makes text bold

   c) Changes font color

   d) Underlines text

6. Which method is used to hide gridlines in a sheet?

   a) hideGridlines(true)

   b) setHiddenGridlines(true)

   c) removeGridlines()

   d) gridlines(false)

7. The merge() method is used to:

   a) Combine two sheets

   b) Combine cells into one

   c) Merge data from two columns

   d) Join two spreadsheets

8. What does SpreadsheetApp.flush() do?

   a) Deletes all data

   b) Applies all pending changes immediately

   c) Refreshes the spreadsheet

   d) Closes the spreadsheet

9. In conditional formatting, whenNumberGreaterThanOrEqualTo() is used for:

   a) Text comparison

   b) Numeric comparison

   c) Date comparison

   d) Boolean comparison

10. The setBackground() method accepts color in which format?

   a) RGB values only

   b) Color names only

   c) Hexadecimal codes

   d) All of the above

SECTION B: FILL IN THE BLANKS (10 × 1 = 10 Marks)

1. The _____________ method is used to insert a new sheet in a spreadsheet.

2. To set headers with bold font, we use the _____________ method.

3. The _____________ function is used to lookup values in a table arranged vertically.

4. To resize columns automatically, we use the _____________ method.

5. The _____________ method is used to set horizontal alignment of text in cells.

6. Conditional formatting rules are created using _____________ method.

7. To set borders around cells, we use the _____________ method.

8. The _____________ method is used to set font color in a cell.

9. To get a range of cells, we use the _____________ method.

10. The _____________ parameter in VLOOKUP determines exact or approximate match.

SECTION C: TRUE OR FALSE (10 × 1 = 10 Marks)

1. The clear() method permanently deletes a sheet from the spreadsheet.

2. getRange("A1:G1") selects cells from A1 to G1.

3. VLOOKUP can search for values in both rows and columns.

4. autoResizeColumns() method requires start column and number of columns as parameters.

5. Conditional formatting can only apply background colors, not font colors.

6. The IF function in spreadsheets can return different values based on conditions.

7. setValues() method can set values for multiple cells at once.

8. A sheet can have only one conditional formatting rule applied.

9. The SUM function adds up all numbers in a specified range.

10. ROUND function can specify the number of decimal places.

SECTION D: SHORT ANSWER QUESTIONS 

                                                   (5 × 2 = 10 Marks)

1. What is the difference between getSheetByName() and insertSheet() methods?

2. Explain the purpose of the VLOOKUP function with its basic syntax.

3. What are the parameters required in the setBorder() method?

4. Write the syntax to set a cell value and apply background color in one statement.

5. What is the purpose of using SpreadsheetApp.flush() in a script?

SECTION E: DETAILED QUESTIONS 

                                                   (5 × 3 = 15 Marks)

1. Explain the working of conditional formatting in Google Sheets. How can you create a rule that highlights cells with values less than 40 in red?

2. Describe the process of creating headers in a sheet and formatting them with bold font and background color. Write the code snippet.

3. What is the significance of the fourth parameter (FALSE/TRUE) in VLOOKUP? Explain with an example how it affects the search results.

4. Write a code snippet to create a new sheet named "Student_Marks" and add headers: "Roll No", "Name", "Marks" with bold formatting.

5. Explain how the IF function works in spreadsheets. Provide an example where it checks if percentage is greater than or equal to 40 to display PASS or FAIL.

SECTION F: LONG ANSWER QUESTIONS (5 × 5 = 25 Marks)

1. Write a complete Google Apps Script function to:

   - Create a new sheet named "Employee_Data"

   - Add headers: "ID", "Name", "Department", "Salary"

   - Insert at least 3 rows of sample data

   - Apply bold formatting and background color to headers

   - Auto-resize all columns

2. Explain the complete structure of a report card system. Describe how VLOOKUP is used to fetch student details (Name, Class, Section) and marks (Maths, Science, English) from a master data sheet. Include the formula structure.

3. Describe in detail how to implement conditional formatting rules in Google Apps Script. Write code to create three rules:

   - Green background for marks >= 90

   - Yellow background for marks between 40-89

   - Red background for marks < 40

4. Create a complete function that:

   - Accepts a sheet name as parameter

   - Checks if the sheet exists

   - If exists, clears it; if not, creates a new sheet

   - Adds a merged header cell with text "REPORT CARD"

   - Hides gridlines for better appearance

   - Returns the sheet object

5. Explain the complete workflow of building an automated report card system:

   - Master data sheet structure

   - Report card sheet layout

   - Use of VLOOKUP for data retrieval

   - Calculation of total and percentage

   - Result determination using IF function

   - Application of conditional formatting

   Provide code snippets for each major step.

________________________________________________________________________________

END OF PAPER