Extending Google Sheets

  1. Custom Functions in Google Sheets
  2. Custom Menus and User Interfaces in Google Sheets
  3. Basics of Storing Data in Google Sheets
  4. Reading Data from a Spreadsheet into JavaScript Objects
    1. Setting up a spreadsheet and the example script
    2. Reading tables row-by-row into JavaScript Objects
    3. Understanding how getRowsData works
    4. Reading tables column-by-column into JavaScript Objects
    5. Full Sample Code
  5. Writing Data from JavaScript Objects to a Spreadsheet
    1. Setting up a spreadsheet and the example script
    2. Writing data to a spreadsheet
    3. Understanding how setRowsData works
    4. Full sample code

Custom Functions in Google Sheets

If the built-in spreadsheet functions of Google Sheets aren't enough for your needs, you can write custom functions with Apps Script.

A custom function (sometimes called a user-defined function) is similar to a spreadsheet formula like =SUM(A1:A5) except that you define the formula’s behavior in Apps Script. For example, you could create a custom function, in2mm(), that converts a value from inches to millimeters, then use the formula in your spreadsheet by typing =in2mm(A1) or =in2mm(10) into a cell.

To learn more about custom functions, try the Menus, Macros, and Custom Functions 5-minute quickstart, or take a look at the more in-depth Custom Functions in Spreadsheets tutorial.

You can easily customize Google Sheets by adding custom menus and dialog boxes. To learn the basics of creating menus, see the guide to menus. To learn about customizing the content of a dialog box, see the guide to Html Service or the guide to Ui Service.

You can also attach a script function to an image or drawing within a spreadsheet; the function will execute when a user clicks on the image or drawing. To learn more, see Images and Drawings in Google Sheets.

Basics of Storing Data in Google Sheets

Storing data in a spreadsheet is most commonly done for scripts which are bound to a spreadsheet, since those scripts have a built-in notion of an active spreadsheet. The examples in this document are referring to a script bound to a spreadsheet. However, standalone scripts can also store data in spreadsheets, though they will need to explicitly specify a spreadsheet to open and use, via SpreadsheetApp.openById().

Google Sheets has some limits that you should take into account when deciding whether or not to use a spreadsheet to store your script's data. For example, a spreadsheet is limited to 400,000 total cells across all sheets and 256 columns per sheet. See this help center article for the full listing of limits.

The simplest way of saving and retrieving data in a spreadsheet is to simply save each piece of data in its own cell. You can launch the Script Editor from a spreadsheet by choosing Tools > Script Editor.

Suppose you have a list of product names and product numbers that you store in a spreadsheet, as shown in the image below.

As you can see, the product names are stored in column A and the product numbers are stored in column B.

To retrieve the data from the spreadsheet, you must get access to the spreadsheet where the data is stored, get the range in the spreadsheet that holds the data, and then get the values of the cells. The example below shows how to retrieve and log the product names and product numbers.

function printProductInfo() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i < data.length; i++) {
    Logger.log("Product name: " + data[i][0]);
    Logger.log("Product number: " + data[i][1]);
  }
 }

To view the data that has been logged, choose View > Logs... from the Script Editor.

To store data, such as a new product name and number to the spreadsheet, you could add the following code to the script.

function addProduct() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(["Cotton Sweatshirt XL", "css004"]);
}

This code appends a new row at the bottom of the spreadsheet, with the values specified. If you run this function, you'll see a new row added to the spreadsheet.

Reading Data from a Spreadsheet into JavaScript Objects

This section guides you through the steps of easily reading structured data from a spreadsheet and creating JavaScript objects to facilitate access to the data. It provides some useful helper functions, such as getRowsData, that can be reused in your scripts.

Setting up a spreadsheet and the example script

  1. Open the spreadsheet template for this tutorial.
  2. Create your own copy of the spreadsheet by clicking on the File > Make a copy. You can now edit the spreadsheet and start writing scripts.
  3. Open the Script Editor from Tools > Script Editor.
  4. You should see the 'reading_data' file in the project.
  5. Run the function runExample. You should see a message box that shows the information of the third employee in the spreadsheet table.

In the next section, you'll learn how the script reads in the data from the spreadsheet.

Reading tables row-by-row into JavaScript Objects

First, find the code for the runExample function in the Script Editor. 
// Get the range of cells that store employee data.
var employeeDataRange = ss.getRangeByName("employeeData");

Google Sheets lets users define Named Ranges of cells. In this example, the range "employeeData" has been defined as "A2:E45". You can verify this by going to the spreadsheet, clicking on Data > Named and protected ranges, and then selecting 'employeeData'.

Named ranges are very convenient and flexible when writing spreadsheet scripts, and you should consider using them instead of hard coding ranges in scripts. Read more information about Named Ranges here.

// For every row of employee data, generate an employee object.
var employeeObjects = getRowsData(sheet, employeeDataRange);

The function getRowsData is defined just below runExample and is where most of the work is done in this example. You are encouraged to reuse it and tweak it in your future scripts.

  var thirdEmployee = employeeObjects[2];

At this point, all data from the the table in the spreadsheet has been read into employeeObjects. For every row in the table, a JavaScript object has been created. The code above retrieves the information about the third employee in the table (Maryanne Packard). Note that Arrays in JavaScript are 0-based, so the first entry is employeeObjects[0].

var stringToDisplay = "The third row is: " + thirdEmployee.firstName + " " + thirdEmployee.lastName;
stringToDisplay += " (id #" + thirdEmployee.employeeId + ") working in the ";
stringToDisplay += thirdEmployee.department + " department and with phone number ";
stringToDisplay += thirdEmployee.phoneNumber;

The code above simply generates a string with all the data from an employee object. Note how column names have become mixed-case object properties (for example, data from the column 'First Name' can be read using object.firstName).

You should now be able to use getRowsData in any of your spreadsheets. Note that you need to manually copy all functions in the example except runExample to your other spreadsheets.

Understanding how getRowsData works

This section explains in detail how the getRowsData function works. It covers both the Spreadsheet service and general JavaScript concepts.

  1. Function getRowsData 
    2. // getRowsData iterates row-by-row on the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
//   - sheet: the sheet object that contains the data to be processed
//   - range: the exact range of cells where the data is stored
//   - columnHeadersRowIndex: specifies the row number where the column names are stored.
//       This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
  columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;

    Function getRowsData takes three arguments. The last one, columnHeadersRowIndex is optional and defaults to the row index of the row immediately above the range argument. This is implemented in the first line of code in the function. If columnHeadersRowIndex is not specified when calling the function, then it is set to range.getRowIndex()-1, where the function getRowIndex returns the index of the first row in a Range object.

    var numColumns = range.getEndColumn() - range.getColumn() + 1;
var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);

    A new Range object is created that contains a single row of data with all the relevant column names.

    var headers = headersRange.getValues()[0];
return getObjects(range.getValues(), normalizeHeaders(headers));

    One way you can quickly debug problems is by creating message boxes to show relevant data. For instance, in the code above, you can insert a line of code that prints the list of column names stored in the headers variable. Note that you need to call the standard JavaScript function toSource to serialize JavaScript Arrays. The resulting code will look like this:

    var headers = headersRange.getValues()[0];
Browser.msgBox(headers.toSource());
return getObjects(range.getValues(), normalizeHeaders(headers));

    The getObjects function is called with the following two parameters:

    • a JavaScript 2-dimensional Array that contains all the values in the range of data. In this example, range.getValues()[0][1] returns data for the first row and second column: "Berger".
    • an Array of normalized names of columns. We will analyze the normalizeHeaders function later.
  2. Function getObjects 
    3. function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}

    The first for loop iterates over each row in the data 2-dimensional array. The second loop iterates over colums in a given row and processes data for a given cell. Every non-empty generated object is appended to an Array of objects that is returned.

    object[keys[j]] = cellData;

    This is the key statement that sets the object property with name keys[j] to the value cellData .

  3. Column name normalization functions 
    // Normalizes a string, by removing all non-alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
//   - header: string to normalize
// Examples:
//   "First Name" -> "firstName"
//   "Market Cap (millions) -> "marketCapMillions
//   "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}

    This function is pure JavaScript. It basically iterates over the input header string character-by-character, removes all non alphanumeric characters and returns a mixed-case string that is easy to use to define JavaScript object properties.

    // Returns an Array of normalized Strings.
// Arguments:
//   - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    }
  }
  return keys;
}

    This function simply normalizes one-by-one all the strings defined in the headers Array of Strings.

Reading tables column-by-column into JavaScript Objects

Now that you feel comfortable using getRowsData, this section explains how you can read data column-by-column instead of row-by-row.

The second sheet in the spreadsheet contains a table with the same data from previous sections, but organized in columns:

How would you implement a version of getColumnsData ? One way to do this is to reuse some of the code described above. 

function runExercise() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[1];

  // Get the range of cells that store employee data.
  var employeeDataRange = sheet.getRange("B1:F5");

  // For every row of employee data, generate an employee object.
  var employeeObjects = getColumnsData(sheet, employeeDataRange);

  var thirdEmployee = employeeObjects[2];
  var stringToDisplay = "The third column is: " + thirdEmployee.firstName + " " + thirdEmployee.lastName;
  stringToDisplay += " (id #" + thirdEmployee.employeeId + ") working in the ";
  stringToDisplay += thirdEmployee.department + " department and with phone number ";
  stringToDisplay += thirdEmployee.phoneNumber;
  Browser.msgBox(stringToDisplay);
}

// Given a JavaScript 2d Array, this function returns the transposed table.
// Arguments:
//   - data: JavaScript 2d Array
// Returns a JavaScript 2d Array
// Example: arrayTranspose([[1,2,3],[4,5,6]]) returns [[1,4],[2,5],[3,6]].
function arrayTranspose(data) {
  if (data.length == 0 || data[0].length == 0) {
    return null;
  }

  var ret = [];
  for (var i = 0; i < data[0].length; ++i) {
    ret.push([]);
  }

  for (var i = 0; i < data.length; ++i) {
    for (var j = 0; j < data[i].length; ++j) {
      ret[j][i] = data[i][j];
    }
  }

  return ret;
}

// getColumnsData iterates column by column in the input range and returns an array of objects.
// Each object contains all the data for a given column, indexed by its normalized row name.
// Arguments:
//   - sheet: the sheet object that contains the data to be processed
//   - range: the exact range of cells where the data is stored
//   - rowHeadersColumnIndex: specifies the column number where the row names are stored.
//       This argument is optional and it defaults to the column immediately left of the range;
// Returns an Array of objects.
function getColumnsData(sheet, range, rowHeadersColumnIndex) {
  rowHeadersColumnIndex = rowHeadersColumnIndex || range.getColumnIndex() - 1;
  var headersTmp = sheet.getRange(range.getRow(), rowHeadersColumnIndex, range.getNumRows(), 1).getValues();
  var headers = normalizeHeaders(arrayTranspose(headersTmp)[0]);
  return getObjects(arrayTranspose(range.getValues()), headers);
}

Running the function runExercise should return the same data as in Section 1:

Full Sample Code

Examples

function runExample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // Get the range of cells that store employee data.
  var employeeDataRange = ss.getRangeByName("employeeData");

  // For every row of employee data, generate an employee object.
  var employeeObjects = getRowsData(sheet, employeeDataRange);

  var thirdEmployee = employeeObjects[2];

  var stringToDisplay = "The third row is: " + thirdEmployee.firstName + " " + thirdEmployee.lastName;
  stringToDisplay += " (id #" + thirdEmployee.employeeId + ") working in the ";
  stringToDisplay += thirdEmployee.department + " department and with phone number ";
  stringToDisplay += thirdEmployee.phoneNumber;

  Browser.msgBox(stringToDisplay);
}

function runExercise() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[1];

  // Get the range of cells that store employee data.
  var employeeDataRange = sheet.getRange("B1:F5");

  // For every row of employee data, generate an employee object.
  var employeeObjects = getColumnsData(sheet, employeeDataRange);

  var thirdEmployee = employeeObjects[2];
  var stringToDisplay = "The third column is: " + thirdEmployee.firstName + " " + thirdEmployee.lastName;
  stringToDisplay += " (id #" + thirdEmployee.employeeId + ") working in the ";
  stringToDisplay += thirdEmployee.department + " department and with phone number ";
  stringToDisplay += thirdEmployee.phoneNumber;
  ss.msgBox(stringToDisplay);
}

Library

// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
//   - sheet: the sheet object that contains the data to be processed
//   - range: the exact range of cells where the data is stored
//   - columnHeadersRowIndex: specifies the row number where the column names are stored.
//       This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
  columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
  var numColumns = range.getLastColumn() - range.getColumn() + 1;
  var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(range.getValues(), normalizeHeaders(headers));
}

// getColumnsData iterates column by column in the input range and returns an array of objects.
// Each object contains all the data for a given column, indexed by its normalized row name.
// Arguments:
//   - sheet: the sheet object that contains the data to be processed
//   - range: the exact range of cells where the data is stored
//   - rowHeadersColumnIndex: specifies the column number where the row names are stored.
//       This argument is optional and it defaults to the column immediately left of the range;
// Returns an Array of objects.
function getColumnsData(sheet, range, rowHeadersColumnIndex) {
  rowHeadersColumnIndex = rowHeadersColumnIndex || range.getColumnIndex() - 1;
  var headersTmp = sheet.getRange(range.getRow(), rowHeadersColumnIndex, range.getNumRows(), 1).getValues();
  var headers = normalizeHeaders(arrayTranspose(headersTmp)[0]);
  return getObjects(arrayTranspose(range.getValues()), headers);
}


// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
//   - data: JavaScript 2d array
//   - keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}

// Returns an Array of normalized Strings.
// Arguments:
//   - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    }
  }
  return keys;
}

// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
//   - header: string to normalize
// Examples:
//   "First Name" -> "firstName"
//   "Market Cap (millions) -> "marketCapMillions
//   "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}

// Returns true if the cell where cellData was read from is empty.
// Arguments:
//   - cellData: string
function isCellEmpty(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}

// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit(char);
}

// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
  return char >= '0' && char <= '9';
}

// Given a JavaScript 2d Array, this function returns the transposed table.
// Arguments:
//   - data: JavaScript 2d Array
// Returns a JavaScript 2d Array
// Example: arrayTranspose([[1,2,3],[4,5,6]]) returns [[1,4],[2,5],[3,6]].
function arrayTranspose(data) {
  if (data.length == 0 || data[0].length == 0) {
    return null;
  }

  var ret = [];
  for (var i = 0; i < data[0].length; ++i) {
    ret.push([]);
  }

  for (var i = 0; i < data.length; ++i) {
    for (var j = 0; j < data[i].length; ++j) {
      ret[j][i] = data[i][j];
    }
  }

  return ret;
}

Writing Data from JavaScript Objects to a Spreadsheet

This section guides you through the steps of easily reading structured data from a spreadsheet and populating a second spreadsheet with different views of the data. It provides a helper function setRowsData that you can reuse in your scripts.

Setting up a spreadsheet and the example script

  1. Open the template spreadsheet for this tutorial.
  2. Create your own copy of the spreadsheet by clicking on File > Make a copy. You can now edit the spreadsheet and start writing scripts.
  3. Open the Script Editor from Tools > Script Editor.
  4. You should see a project with a single file named "writing_data".

The objective of this example is to:

  • Read all the data from this other spreadsheet

  • Dynamically create one Sheet per department name
  • Insert employee data (First Name, Last Name and Department) in the appropriate Sheet

Run the function runExample and check out the results. You should see:

  • 5 different Sheets: Sheet1, then in alphabetical order Engineering, Legal, Marketing, Sales

  • Every department Sheet should contain the information about employees in the that department.

The next section explains how the example code works.

Writing data to a spreadsheet

In this section, we will explain how the runExample function works. Here's the full code for it: 
function runExample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var dataSs = SpreadsheetApp.openById(DATA_SPREADSHEET_ID);
  var dataSheet = dataSs.getSheets()[0];

  // Fetch all the data
  var data = getRowsData(dataSheet);

  // This is the data we want to display
  var columnNames = ["First Name", "Last Name", "Department"];

  // Index data by department name
  var dataByDepartment = {};
  var departments = [];
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    if (!dataByDepartment[rowData.department]) {
      dataByDepartment[rowData.department] = [];
      departments.push(rowData.department);
    }
    dataByDepartment[rowData.department].push(rowData);
  }

  departments.sort();
  var headerBackgroundColor = dataSheet.getRange(1, 1).getBackgroundColor();
  for (var i = 0; i < departments.length; ++i) {
    var sheet = ss.getSheetByName(departments[i]) ||
      ss.insertSheet(departments[i], ss.getSheets().length);
    sheet.clear();
    var headersRange = sheet.getRange(1, 1, 1, columnNames.length);
    headersRange.setValues([columnNames]);
    headersRange.setBackgroundColor(headerBackgroundColor);
    setRowsData(sheet, dataByDepartment[departments[i]]);
  }
}

Here are some comments on the relevant parts: 

  var dataSs = SpreadsheetApp.openById(DATA_SPREADSHEET_ID);

This is how the spreadsheet that contains the data is opened. DATA_SPREADSHEET_ID is the Spreadsheet identified ( 0AlNd4P4KLiq8cktUT0xINDFIT0syZ0xvc2Y3ZDZQMWc ), which can be extracted from this spreadsheet URL 

var data = getRowsData(dataSheet);

For every row of data, a JavaScript object is created. This is explained in more detail in the section on Reading tables row-by-row into JavaScript Objects above. Note that dataSheet is not part of the active spreadsheet. In this example, data looks like: 

  [ {firstName: "John",    lastName: "Berger", employeeId: 3512, department: "Sales",       phoneNumber: "(212) 123-4560"},
    {firstName: "Patrick", lastName: "Benson", employeeId: 1342, department: "Engineering", phoneNumber: "(212) 123-4561"},
    ...
  ]

  // Index data by department name
  var dataByDepartment = {};
  var departments = [];
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    if (!dataByDepartment[rowData.department]) {
      dataByDepartment[rowData.department] = [];
      departments.push(rowData.department);
    }
    dataByDepartment[rowData.department].push(rowData);
  }

The code above simply creates a list of objects per department name. 

departments.sort();

The list of department names is sorted alphabetically, so that all new Sheets are sorted. 

var headerBackgroundColor = dataSheet.getRange(1, 1).getBackgroundColor();

The background color of the header cells in the data Sheet is extracted to be reused in the dynamically generated Sheets. 

  for (var i = 0; i < departments.length; ++i) {
    var sheet = ss.getSheetByName(departments[i]) ||
      ss.insertSheet(departments[i], ss.getSheets().length);
    sheet.clear();
    var headersRange = sheet.getRange(1, 1, 1, columnNames.length);
    headersRange.setValues([columnNames]);
    headersRange.setBackgroundColor(headerBackgroundColor);
    setRowsData(sheet, dataByDepartment[departments[i]]);
  }

For every department name found in the data Sheet:

  • Locate the sheet which matches the name (or create a new one)
  • Clear the contents of the sheet
  • Set the column names in the first row of the sheet and set their background color
  • For every Object in dataByDepartment[departments[i]], fill in one row of data in the sheet. See details about setRowsData in the next section

Understanding how setRowsData works

This section explains in detail how setRowsData works.

// setRowsData fills in one row of data per object defined in the objects Array.
// For every Column, it checks if data objects define a value for it.
// Arguments:
//   - sheet: the Sheet Object where the data will be written
//   - objects: an Array of Objects, each of which contains data for a row
//   - optHeadersRange: a Range of cells where the column headers are defined. This
//     defaults to the entire first row in sheet.
//   - optFirstDataRowIndex: index of the first row where data should be written. This
//     defaults to the row immediately below the headers.
function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) {
  var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns());
  var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1;

Compute the Range of cells that contain the column headers if none is specified. Compute the index of the first data row if none is specified.

  var headers = normalizeHeaders(headersRange.getValues()[0]);

Normalized headers are the names of object properties. Note that the Array of Strings may contain empty Strings.

  var data = [];
  for (var i = 0; i < objects.length; ++i) {
    var values = []
    for (j = 0; j < headers.length; ++j) {
      var header = headers[j];
      // If the header is non-empty and the object value is 0...
      if ((header.length > 0) && (objects[i][header] == 0)) {
        values.push(0);
      }
      // If the header is empty or the object value is empty...
      else if ((!(header.length > 0)) || (objects[i][header]=='')) {
        values.push('');
      }
      else {
        values.push(objects[i][header]);
      }
    }
    data.push(values);
  }

This loop generates a JavaScript 2-dimensional Array that correspond to values of spreadsheet cells to write. For each object, for each column check if the object defines a property corresponding to the column name. If it does, fill in the property value, otherwise fill in with an empty string.

  var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(),
                                        objects.length, headers.length);

Compute the Range of cells where data should be dumped based on the first data row index and the number of objects.

  destinationRange.setValues(data);

Set the values of the spreadsheet cells from the 2-dimensional JavaScript Array generated above. Note that rows of data below destinationRange will not be cleared.

Full Sample Code

Example Code

// This is where the data used in this example will be retrieved from:
// https://docs.google.com/spreadsheet/ccc?key=0AlNd4P4KLiq8cktUT0xINDFIT0syZ0xvc2Y3ZDZQMWc#gid=0
var DATA_SPREADSHEET_ID = "0AlNd4P4KLiq8cktUT0xINDFIT0syZ0xvc2Y3ZDZQMWc"


function runExample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var dataSs = SpreadsheetApp.openById(DATA_SPREADSHEET_ID);
  var dataSheet = dataSs.getSheets()[0];

  // Fetch all the data
  var data = getRowsData(dataSheet);

  // This is the data we want to display
  var columnNames = ["First Name", "Last Name", "Department"];

  // Index data by department name
  var dataByDepartment = {};
  var departments = [];
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    if (!dataByDepartment[rowData.department]) {
      dataByDepartment[rowData.department] = [];
      departments.push(rowData.department);
    }
    dataByDepartment[rowData.department].push(rowData);
  }

  departments.sort();
  var headerBackgroundColor = dataSheet.getRange(1, 1).getBackgroundColor();
  for (var i = 0; i < departments.length; ++i) {
    var sheet = ss.getSheetByName(departments[i]) ||
      ss.insertSheet(departments[i], ss.getSheets().length);
    sheet.clear();
    var headersRange = sheet.getRange(1, 1, 1, columnNames.length);
    headersRange.setValues([columnNames]);
    headersRange.setBackgroundColor(headerBackgroundColor);
    setRowsData(sheet, dataByDepartment[departments[i]]);
  }
}

SetRowsData

// setRowsData fills in one row of data per object defined in the objects Array.
// For every Column, it checks if data objects define a value for it.
// Arguments:
//   - sheet: the Sheet Object where the data will be written
//   - objects: an Array of Objects, each of which contains data for a row
//   - optHeadersRange: a Range of cells where the column headers are defined. This
//     defaults to the entire first row in sheet.
//   - optFirstDataRowIndex: index of the first row where data should be written. This
//     defaults to the row immediately below the headers.
function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) {
  var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns());
  var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1;
  var headers = normalizeHeaders(headersRange.getValues()[0]);

  var data = [];
  for (var i = 0; i < objects.length; ++i) {
    var values = []
    for (j = 0; j < headers.length; ++j) {
      var header = headers[j];
      // If the header is non-empty and the object value is 0...
      if ((header.length > 0) && (objects[i][header] == 0)) {
        values.push(0);
      }
      // If the header is empty or the object value is empty...
      else if ((!(header.length > 0)) || (objects[i][header]=='')) {
        values.push('');
      }
      else {
        values.push(objects[i][header]);
      }
    }
    data.push(values);
  }

  var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(),
                                        objects.length, headers.length);
  destinationRange.setValues(data);
}

Code reused from Reading Spreadsheet Data using JavaScript Objects

// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
//   - sheet: the sheet object that contains the data to be processed
//   - range: the exact range of cells where the data is stored
//       This argument is optional and it defaults to all the cells except those in the first row
//       or all the cells below columnHeadersRowIndex (if defined).
//   - columnHeadersRowIndex: specifies the row number where the column names are stored.
//       This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
  var headersIndex = columnHeadersRowIndex || range ? range.getRowIndex() - 1 : 1;
  var dataRange = range ||
    sheet.getRange(headersIndex + 1, 1, sheet.getMaxRows() - headersIndex, sheet.getMaxColumns());
  var numColumns = dataRange.getLastColumn() - dataRange.getColumn() + 1;
  var headersRange = sheet.getRange(headersIndex, dataRange.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(dataRange.getValues(), normalizeHeaders(headers));
}

// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
//   - data: JavaScript 2d array
//   - keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}

// Returns an Array of normalized Strings.
// Empty Strings are returned for all Strings that could not be successfully normalized.
// Arguments:
//   - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    keys.push(normalizeHeader(headers[i]));
  }
  return keys;
}

// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
//   - header: string to normalize
// Examples:
//   "First Name" -> "firstName"
//   "Market Cap (millions) -> "marketCapMillions
//   "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}

// Returns true if the cell where cellData was read from is empty.
// Arguments:
//   - cellData: string
function isCellEmpty(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}

// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit(char);
}

// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
  return char >= '0' && char <= '9';
}

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies.

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.