Run your first Sheets script

Read a sample sheet

Create a bound script that reads a Customers tab and writes its rows to the execution log. The script does not change the spreadsheet. You need a Google account and a new, empty spreadsheet. The three sample rows are below.

function readAllCustomerRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
  if (!sheet) throw new Error('Add a Sheet tab named Customers before running this function.');
  const values = sheet.getDataRange().getValues();
  Logger.log(values);
}

getDataRange() selects every cell on the tab that holds data, as one rectangle. getValues() returns that rectangle as an array of rows, and Logger.log() writes the array to the execution log. See the Range reference for the read methods and their Sheets scopes, and the Logger reference for the log call.

Set up the spreadsheet

1. Create a new blank Google Sheet for this exercise. Do not use a live customer spreadsheet.

2. Create or rename its first tab to Customers.

3. Copy the comma-separated text from sample.csv, shown below, into cell A1, or import the file with File > Import.

Name,Email,Status
Avery Quinn,avery@example.com,Active
Morgan Lee,morgan@example.com,Paused
Riley Park,riley@example.com,Active

4. If you pasted the text, select column A, choose Data > Split text to columns, and set the separator to Comma.

5. Check that Customers!A1:C4 holds four rows: the Name, Email, and Status headers, then three customers.

6. In that spreadsheet, choose Extensions > Apps Script. Replace the default code in Code.gs with the complete Code.gs example file.

7. In Project Settings, select Show "appsscript.json" manifest file in editor, then replace its contents with the project manifest (appsscript.json):

{
  "timeZone": "Etc/UTC",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]
}

8. Save both files.

The project is bound to this spreadsheet, so getActiveSpreadsheet() returns the parent file. A standalone project would need to open a spreadsheet by ID or URL instead. See Google's bound-script guide.

Run the entrypoint

1. Choose readAllCustomerRows in the function menu at the top of the Apps Script editor.

2. Click Run.

3. Read the permission prompt. It should ask only for access to the current spreadsheet, because that is the only scope in the manifest.

4. Open Execution log after the run finishes.

The function runs as you, the signed-in user. Apps Script checks which required scopes you have already granted. It asks for consent if any are missing. Google describes this in its authorization guide.

Recognize the result

The log shows the rows from the sheet, including the header row:

[
  ["Name", "Email", "Status"],
  ["Avery Quinn", "avery@example.com", "Active"],
  ["Morgan Lee", "morgan@example.com", "Paused"],
  ["Riley Park", "riley@example.com", "Active"]
]

The editor may format the log differently, but it shows four rows with three values each. This two-dimensional array contains one inner array of cell values for each row.

Handle ordinary failures

• If the run stops with Add a Sheet tab named Customers before running this function., rename or create the tab Customers, then run again. Without that guard, calling getDataRange() on a missing tab fails with Cannot read properties of null.

• If the authorization dialog requests broader access than the current spreadsheet, cancel it. Check that appsscript.json contains the current-spreadsheet scope and that you have not added other service calls to the project.

• If each logged row has only one value, the pasted text was never split. Select column A and use Data > Split text to columns with Comma as the separator, then run again.

Change the input

Change Paused in the sample sheet to Active and run readAllCustomerRows again. The logged value for Morgan changes from Paused to Active.

Clean up and continue

When you no longer need the disposable spreadsheet, delete it. Deleting the spreadsheet also removes its bound script. Next, read Work with JavaScript values and objects so your script can inspect each row and decide what to do with it.