Creating and opening spreadsheets in Google Sheets - Practice Exercises

Challenge - 5 Problems

📊 Formula Result

intermediate

Opening a spreadsheet by URL

You want to open a Google Sheets spreadsheet using its URL in Apps Script. Which line of code correctly opens the spreadsheet?

A SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/abc123')

B SpreadsheetApp.open('https://docs.google.com/spreadsheets/d/abc123')

C SpreadsheetApp.getByUrl('https://docs.google.com/spreadsheets/d/abc123')

D SpreadsheetApp.openById('https://docs.google.com/spreadsheets/d/abc123')

💡 Hint

Remember the method name includes 'ByUrl' when opening by URL.

The correct method to open a spreadsheet by its URL is SpreadsheetApp.openByUrl(url). Other methods either don't exist or require different parameters.

❓ Function Choice

intermediate

Creating a new spreadsheet with a name

Which function creates a new Google Sheets spreadsheet with the name 'Budget 2024'?

A SpreadsheetApp.open('Budget 2024')

B SpreadsheetApp.new('Budget 2024')

C SpreadsheetApp.make('Budget 2024')

D SpreadsheetApp.create('Budget 2024')

💡 Hint

The method to create a new spreadsheet is named 'create'.

SpreadsheetApp.create(name) creates a new spreadsheet with the given name. Other options are invalid methods.

🎯 Scenario

advanced

Accessing a spreadsheet by ID and getting its name

You have the spreadsheet ID '1a2b3c4d5e'. Which code snippet correctly opens the spreadsheet and gets its name?

A var ss = SpreadsheetApp.open('1a2b3c4d5e'); var name = ss.getTitle();

B var ss = SpreadsheetApp.openById('1a2b3c4d5e'); var name = ss.getName();

C var ss = SpreadsheetApp.getById('1a2b3c4d5e'); var name = ss.getName();

D var ss = SpreadsheetApp.openByUrl('1a2b3c4d5e'); var name = ss.getName();

💡 Hint

Use the method that opens by ID and the method that returns the spreadsheet's name.

SpreadsheetApp.openById(id) opens the spreadsheet by its ID. The method getName() returns the spreadsheet's name. Other methods are invalid or incorrect.

📊 Formula Result

advanced

Result of creating and accessing a new spreadsheet

What is the output of this Apps Script code snippet? var ss = SpreadsheetApp.create('Test Sheet'); var name = ss.getName(); Logger.log(name);

Google Sheets

var ss = SpreadsheetApp.create('Test Sheet');
var name = ss.getName();
Logger.log(name);

A SpreadsheetApp

B undefined

C Test Sheet

D Error: Method getName() not found

💡 Hint

The getName() method returns the name of the spreadsheet.

The code creates a new spreadsheet named 'Test Sheet' and then gets its name, which is 'Test Sheet'.

❓ data_analysis

expert

Identifying error when opening spreadsheet by URL with wrong method

What error will this Apps Script code produce? var ss = SpreadsheetApp.open('https://docs.google.com/spreadsheets/d/abc123');

Google Sheets

var ss = SpreadsheetApp.open('https://docs.google.com/spreadsheets/d/abc123');

A TypeError: SpreadsheetApp.open is not a function

B ReferenceError: open is not defined

C SyntaxError: Unexpected string

D No error, spreadsheet opens successfully

💡 Hint

Check if the method 'open' exists in SpreadsheetApp.

SpreadsheetApp does not have an 'open' method. The correct method to open by URL is 'openByUrl'. Using 'open' causes a TypeError.

Practice

1. What does creating a spreadsheet mean in Google Sheets?

easy

A. Sharing a file with others

B. Deleting an existing file

C. Printing a document

D. Starting a new file to enter data

Solution

1. Step 1: Understand the meaning of creating a spreadsheet

   Creating means making a new file where you can add data.

2. Step 2: Compare options with the meaning

   Only Starting a new file to enter data matches starting a new file to enter data.

3. Final Answer:

   Starting a new file to enter data -> Option D

4. Quick Check:

   Creating spreadsheet = new file [OK]

Hint: Creating means making a new file, not editing or deleting [OK]

Common Mistakes:

• Confusing creating with opening an existing file
• Thinking creating means sharing or printing
• Mixing up deleting with creating

2. Which of these is the correct way to open an existing spreadsheet in Google Sheets?

easy

A. Click 'File' > 'New' > 'Spreadsheet'

B. Go to Google Drive and double-click the spreadsheet file

C. Type a formula to open the file

D. Use the 'Insert' menu to open a spreadsheet

Solution

1. Step 1: Identify how to open existing files

   Opening means accessing a file already saved, usually by clicking it in Google Drive.

2. Step 2: Check options for correct method

   Go to Google Drive and double-click the spreadsheet file correctly describes opening by double-clicking the file in Google Drive.

3. Final Answer:

   Go to Google Drive and double-click the spreadsheet file -> Option B

4. Quick Check:

   Open spreadsheet = double-click file in Drive [OK]

Hint: Open files by clicking them in Google Drive, not by formulas [OK]

Common Mistakes:

• Trying to open files using formulas
• Confusing 'New' with opening existing files
• Looking for open option inside 'Insert' menu

3. You want to start a new spreadsheet in Google Sheets. Which action will open a blank spreadsheet ready for data entry?

medium

A. Click the '+ Blank' button on the Google Sheets homepage

B. Type =NEW() in any cell

C. Right-click a cell and select 'Open Spreadsheet'

D. Use the 'Format' menu to create a new sheet

Solution

1. Step 1: Identify how to create a new spreadsheet

   On Google Sheets homepage, the '+ Blank' button creates a new empty spreadsheet.

2. Step 2: Evaluate other options for correctness

   Options A, B, and C are incorrect because the 'Format' menu does not create new spreadsheets, formulas can't create files, and right-click doesn't open new files.

3. Final Answer:

   Click the '+ Blank' button on the Google Sheets homepage -> Option A

4. Quick Check:

   New spreadsheet = '+ Blank' button [OK]

Hint: Use '+ Blank' button to start new spreadsheet, not formulas [OK]

Common Mistakes:

• Trying to create files with formulas
• Looking for new file option in wrong menus
• Confusing sheet tabs with new spreadsheets

4. You tried to open a spreadsheet by typing =OPEN("MyFile") in a cell, but it shows an error. What is the problem?

medium

A. The file name must be in lowercase

B. You need to use the IMPORT formula instead

C. Google Sheets does not have an OPEN formula to open files

D. You must enable file opening in settings first

Solution

1. Step 1: Understand formula limitations in Google Sheets

   Google Sheets formulas cannot open or create files; they only work with data inside sheets.

2. Step 2: Analyze the error cause

   The formula =OPEN() does not exist, so it causes an error.

3. Final Answer:

   Google Sheets does not have an OPEN formula to open files -> Option C

4. Quick Check:

   No OPEN formula exists in Google Sheets [OK]

Hint: Files open via interface, not formulas like =OPEN() [OK]

Common Mistakes:

• Assuming formulas can open files
• Trying to fix error by changing file name case
• Looking for settings to enable file opening by formula

5. You want to organize your work by creating a new spreadsheet and then sharing it with your team. What is the correct sequence of actions?

hard

A. Create a new spreadsheet via '+ Blank', then click 'Share' to add team emails

B. Open an existing spreadsheet, then use a formula to share it

C. Type =SHARE() in a cell after creating a spreadsheet

D. Create a new spreadsheet, then download and email it manually

Solution

1. Step 1: Create a new spreadsheet

   Use the '+ Blank' button on Google Sheets homepage to start a new file.

2. Step 2: Share the spreadsheet with your team

   Click the 'Share' button in the top-right corner and enter team members' emails.

3. Step 3: Evaluate other options

   Open an existing spreadsheet, then use a formula to share it is wrong because opening existing file is not creating new. Type =SHARE() in a cell after creating a spreadsheet is wrong because no SHARE formula exists. Create a new spreadsheet, then download and email it manually is possible but less efficient than direct sharing.

4. Final Answer:

   Create a new spreadsheet via '+ Blank', then click 'Share' to add team emails -> Option A

5. Quick Check:

   Create then Share = '+ Blank' then 'Share' button [OK]

Hint: Create new file first, then share via button, not formulas [OK]

Common Mistakes:

• Trying to share using formulas
• Opening old files instead of creating new
• Downloading instead of sharing online