GSheetsUtils

Global

Methods

getSheetById(spreadsheet, sheetId)

getSheetById returns a Sheet object when given a Spreadsheet object and sheet Id

Parameters:
Name Type Description
spreadsheet Spreadsheet

the Spreadsheet object where you expect the sheet with the given ID to live
sheetId string

the id of the Sheet you want to get. Can be of type number or string, as long as it can be cast as numeric
Returns:
Type
Sheet
Example

Useful when you need to find a sheet by its immutable Sheet Id. No method available for this exists in native SpreadsheetApp. 

function getStoredSheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheetId = PropertiesService.getDocumentProperties().getProperty('sheetId', sheetId);
    var sheet = GSheetsUtils.getSheetById(ss, sheetId);
    return sheet;
  }

convert2DArrayToObjects(data, keys)

For every row of data in 2-d array, generates an object that contains the data.

Parameters:
Name Type Description
data Array.<Array.<string>>

JavaScript 2d array representing spreadsheet data
keys Array.<string>

Array of strings that define the property names for the objects to create
Returns:
  • Array of keyed data objects
Type
Array.<Object>
Example

Take a 2-D array data the way it comes from .getValues() and change it into array of objects with specified keys. Generally not used in most Amplified Labs tools. Instead we tend to use getRowsData() method. 

function convertArrayOfQuizScores() {
     var headers = ['Quiz 1','Quiz 2','Quiz 3'];
     var data = [[93, 87, 75],[91, 83, 71],[61, 63, 70]];
     var objects = GSheetsUtils.convertQuizScores(data, headers);
     debugger;
   }

   returns [
     {"Quiz 1": 93, "Quiz 2": 91, "Quiz 3": 61},
     {"Quiz 1": 87, "Quiz 2": 83, "Quiz 3": 63},
     {"Quiz 1": 75, "Quiz 2": 71, "Quiz 3": 70}
   ]

getUpsertHeaders(sheet [, optParams])

getUpsertHeaders checks the designated sheet for the existence of the expected headers, sets / upserts any missing headers in the same row as the headers range (default value of row 1) optionally freezes header row(s) returns array of amended headers

Parameters:
Name Type Argument Description
sheet Sheet

the Sheet Object where the headers will be read and/or written
optParams Object <optional>

optional config parameters

Properties
Name Type Argument Description
expectedHeaders Array.<string> <optional>

1-d array of headers you want to insert, or that you expect to exist in the sheet
freezeHeaders Boolean <optional>

Boolean whether you want to freeze header row(s). Defaults to false.
Tutorials:
Returns:
  • 1-D array of headers.
Type
Array.<string>
Example

A convenience method to fetch existing headers and also set / repair headers. 

function getStatusColumnNumber() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Students');
    var headers = GSheetsUtils.getUpsertHeaders(sheet);
    //headers are returned as 1-D array
    var colNum = headers.indexOf('Status') + 1;
    return colNum;
 }

 function getStatusColumnNumberWithSetFix() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Students');
    var expected = ['First Name','Last Name','Status'];
    var headers = GSheetsUtils.getUpsertHeaders(sheet, {expectedHeaders: expected, freezeHeaders: true});
    //headers are set / fixed in sheet and returned as 1-D array.
    //existing headers not in expected headers array are not disturbed.
    var colNum = headers.indexOf('Status') + 1;
    return colNum;
 }

getRowsData(sheet [, optParams])

getRowsData iterates row by row in the input range and returns an array of objects. Assumes that headers in the destination sheet are also the keys of the source data Each object contains all the data for a given row, indexed by its column name.

Parameters:
Name Type Argument Description
sheet Sheet

the sheet object that contains the data to be processed
optParams Object <optional>

optional config parameters

Properties
Name Type Argument Description
dataRange Range <optional>

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).
headersRange Range <optional>

the range that the headers are in - use to limit which columns to read from
columnHeadersRowIndex rowNum <optional>

specifies the row number where the column names are stored. This argument is optional and it defaults to the row immediately above range;
Tutorials:
Returns:
  • array of objects keyed to sheet headers
Type
Array.<Object>
Example
function basicUsage() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName('Classes');
   var classes = GSheetsUtils.getRowsDate(sheet);
   debugger;
   //show array of all sheet data with sheet headers as keys
}

function moreAdvancedUsage() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName('Students');
   var headerRowNum = 3; //Assume headers aren't in first row
   var numColsToInclude = 8; //Assume we want to exclude data from columns after column 8
   var dataStartRow = 10; //Assume data doesn't actually start in row 4
   var lastRow = sheet.getLastRow();
   if (lastRow - 9 > 0) { //check that data actually exists in/below dataStartRow
   var dataRange = sheet.getRange(10, 1, lastRow - 9, numColsToInclude);
   var headersRange = sheet.getRange(headerRowNum, 1, 1, numColsToInclude);
   var data = GSheetsUtils.getRowsData(sheet, {dataRange: dataRange, headersRange: headersRange, columnHeadersRowIndex: headerRowNum});
   } else {
     data = [];
   }
   debugger;
   //shows array of all sheet data (as objects) starting in row 10, for the first 8 columns in the sheet
   //where the column headers are in row 3

}

setRowsData(sheet, objects [, optParams])

setRowsData fills in one row of data per object defined in the objects Array. Assumes that headers in the destination sheet are also the keys of the source data For every Column, it checks if data objects define a value for it.

Parameters:
Name Type Argument Description
sheet Sheet

the Sheet Object where the data will be written
objects Array.<Object>

an Array of Objects, each of which contains data for a row
optParams Object <optional>

optional config parameters

Properties
Name Type Argument Default Description
headersRange Range <optional>
 First Row

a Range of cells where the column headers are defined. This defaults to the entire first row in sheet.
firstDataRowIndex rowNum <optional>
 Row immediately below headersRange

index of the first row where data should be written. This defaults to the row immediately below the headers.
Tutorials:
Example
//assume sheet contains headers that match the keys of the objects below...

   var dataToWrite = [
     {"Quiz 1": 93, "Quiz 2": 91, "Quiz 3": 61},
     {"Quiz 1": 87, "Quiz 2": 83, "Quiz 3": 63},
     {"Quiz 1": 75, "Quiz 2": 71, "Quiz 3": 70}
   ];

   GSheetsUtils.setRowsData(sheet, dataToWrite);
   //Voila!
   //Explore optParams to get more advanced applications
   //(e.g. headers not in row 1, data writes starting in arbitrary row, etc.)

appendRowsData(sheet, objects [, optParams])

appendRowsData adds an array of JSON objects to the end of a sheet with headers that match the JSON keys

Parameters:
Name Type Argument Description
sheet Sheet

the Sheet Object where the data will be written
objects Array.<Object>

an array of Objects representing a spreadsheet row
optParams Object <optional>

optional config parameters

Properties
Name Type Argument Default Description
headersRange Range <optional>
 First row

a Range of cells where the column headers are defined. This defaults to the entire first row in sheet.
Example
//assume sheet contains headers that match the keys of the objects below...

   var dataToWrite = [
     {"Quiz 1": 93, "Quiz 2": 91, "Quiz 3": 61},
     {"Quiz 1": 87, "Quiz 2": 83, "Quiz 3": 63},
     {"Quiz 1": 75, "Quiz 2": 71, "Quiz 3": 70}
   ];

   GSheetsUtils.appendRowsData(sheet, dataToWrite);
   //Voila!
   //Explore optParams to get more advanced applications
   //(e.g. headers not in row 1, data writes starting in arbitrary row, etc.)

updateSingleRowInPlace(sheet, sourceObject, rowNum [, optParams])

updateSingleRowInPlace updates key-value pairs in one row of a sheet by using a row number and an optional match key requirement, specified in a params object via a boolean flag an array of column keys. Performance note: Generally efficient if row number approach and matching doesn't fallback. Gets expensive if a row-shift has occurred in the sheet. Assumes that headers in the destination sheet are also the keys of the source data If the match condition fails on the specified, iterates through all destination data and writes each updates to the sheet individually on all matches. If no match requirement is supplied, updates sheet purely based on row number.

Parameters:
Name Type Argument Description
sheet Sheet

the GAS Sheet Object where the data will be written.
sourceObject Object

a single Object containing the key-value pairs to be used for updating matching records
rowNum Number

the row number of the record you are updating. Required.
optParams Object <optional>

optional config parameters

Properties
Name Type Argument Default Description
requireMatch Object <optional>
 false

T/F whether to double check that the record matches based on match criteria
matchHeaders Object <optional>

a string or array of string values representing the shared headers to match on
headersRange Range <optional>
 First row

used to specify which range in the destination sheet holds the headers. Expects a GAS Range object.
Example
//assume sheet contains headers that match the keys of the objects below...
    var updatedRecord = {"Student ID":"23456", "Quiz 1": 91, "Quiz 2": 98, "Quiz 3": 61};
    var rowNum = 5; //row we expect to find the student record in

    GSheetsUtils.updateSingleRowInPlace(sheet, updatedRecord, rowNum, {requireMatch: true, matchHeaders: ['Student ID']});
    //will go to row 5, look for a match on student ID, and write the updated values if it matches.
    //falls back to updateRowsInPlace method if no match is discovered in the row.

updateRowsInPlace(sheet, sourceObject, matchHeaders [, optParams])

updateRowsInPlace updates key-value pairs in one or more rows of a sheet by looking for a match based on a shared header (or headers) and makes updates to matching records. Performance note: This is an expensive method Assumes that headers in the destination sheet are also the keys of the source data Iterates through all destination data and writes each updates to the sheet individually.

Parameters:
Name Type Argument Description
sheet Sheet

the GAS Sheet Object where the data will be written.
sourceObject Object

a single Object containing the key-value pairs to be used for updating matching records
matchHeaders Array.<string>

a string or array of string values representing the shared headers to match on
optParams Object <optional>

optional config parameters

Properties
Name Type Argument Default Description
headersRange Range <optional>
 First row

used to specify which range in the destination sheet holds the headers. Expects a GAS Range object.
requireUnique Boolean <optional>
 false

T/F whether or not to throw an error if query of destination data results in multiple matches
Returns:

updateStatus

Type
updateStatus
Example
//assume sheet contains headers that match the keys of the objects below...
    var updatedRecord = {"Student ID":"23456", "Quiz 1": 91, "Quiz 2": 98, "Quiz 3": 61};

    //row number(s) of matching records are not known in advance.
    //assume students are supposed to be unique in the sheet

    var status = GSheetsUtils.updateRowsInPlace(sheet, updatedRecord, ['Student ID'], {requireUnique: true});
    //will loop throush Sheet looking for a match on student ID, and write the updated values if it matches a row.
    //Throws an error if more than one match is found based on Student ID

updateRowsData(sheet, objects, matchFields [, optParams])

updateRowsData updates a sheet containing existing records with objects defined in the objects Array. Assumes that headers in the destination sheet are also the keys of the source data Requires that source and destination records are unique based on the provided match key or key combination. Optionally inserts records from source if not found in destination. Optionally removes records in destination if not found in source.

Parameters:
Name Type Argument Description
sheet Sheet

the Sheet Object where the data will be written
objects Array.<Object>

an Array of Objects, each of which contains data for a row
matchFields Array.<string>

a String or an Array of column header values that represent the unique key for updating existing records
optParams Object <optional>

optional config parameters

Properties
Name Type Argument Default Description
upsertNewRecords Boolean <optional>
 false

a Boolean, whether or not to insert records from source if not found in destination
removeAndArchiveNonMatchingRecords Boolean <optional>
 false

a Boolean, whether or not to remove data in non-matching rows and archive it in a separate "tabName_archive" tab.
headersRange Range <optional>
 First row

a Range of cells where the column headers are defined. This defaults to the entire first row in sheet.
Returns:

updateStatus

Type
updateStatus
Example
//assume sheet contains headers that match the keys of the objects below...
    var updatedRecords = [
    {"Student ID":"23456", "Quiz 1": 91, "Quiz 2": 98, "Quiz 3": 61},
    {"Student ID":"23457", "Quiz 1": 80, "Quiz 2": 58, "Quiz 3": 90}, //this student doesn't currently exist in sheet
    {"Student ID":"23458", "Quiz 1": 71, "Quiz 2": 90, "Quiz 3": 65}
    ]

    var status = GSheetsUtils.updateRowsData(sheet, updatedRecords, ['Student ID'], {upsertNewRecords: true, removeAndArchiveNonMatchingRecords: true});
    //will update each matching record in the sheet (match based on Student ID) by clearing and rewriting the contents of the entire sheet
    //Based on parameter values...
    //Student 23457 will be upserted (e.g. added) to the Sheet.
    //Student 23459 will be removed from the sheet and archived.

setMappedRowsData(sheet, objects, headerMappings [, optParams])

setMappedRowsData fills in one row of data in a sheet per object defined in an objects Array, with header mappings provided between the source data keys and destination sheet headers For every Column, it checks if data objects define a value for it.

Parameters:
Name Type Argument Description
sheet Sheet

the Sheet Object where the data will be written
objects Array.<Object>

an Array of Objects, each of which contains data for a row
headerMappings Object

a plain Javascript object whose keys are destination headers, and values are source headers.
optParams Object <optional>
Properties
Name Type Argument Description
headersRange Range <optional>

a Range of cells where the column headers are defined. This defaults to the entire first row in sheet.
firstDataRowIndex rowNum <optional>

index of the first row where data should be written. This defaults to the row immediately below the headers.
Example
var headerMappings = {
   "First Name": "firstName",
   "Last Name": "lastName",
   "Status": "updateStatus"
}

var dataToWrite = [
  {firstName:'Joe', lastName: 'Blow', status:'Single'},
  {firstName':'Jane', lastName:'Doe', status:'Deceased'},
  {firstName:'Captain',lastName:'Cook',status:'Lost at sea'}
];

//Sheet contains headers 'First Name','Last Name','Status'

GSheetsUtils.setMappedRowsData(sheet, dataToWrite, headerMappings);
//writes data to sheet based on header mappings...

appendMappedRowsData(sheet, objects, headerMappings [, optParams])

appendRowsData adds an array of JSON objects to the end of a sheet with headers that match the JSON keys

Parameters:
Name Type Argument Description
sheet Sheet

the Sheet Object where the data will be written
objects Array.<Object>

an array of Objects representing spreadsheet rows, where keys may be different than those in the destination spreadsheet
headerMappings Object

a plain Javascript object whose keys are destination headers, and values are source headers.
optParams Object <optional>

optional config parameters

Properties
Name Type Argument Description
headersRange Range <optional>

a Range of cells where the column headers are defined. This defaults to the entire first row in sheet.
Example
var headerMappings = {
   "First Name": "firstName",
   "Last Name": "lastName",
   "Status": "updateStatus"
}

var dataToWrite = [
  {firstName:'Joe', lastName: 'Blow', status:'Single'},
  {firstName':'Jane', lastName:'Doe', status:'Deceased'},
  {firstName:'Captain',lastName:'Cook',status:'Lost at sea'}
];

//Sheet contains headers 'First Name','Last Name','Status'

GSheetsUtils.appendMappedRowsData(sheet, dataToWrite, headerMappings);
//appends data to last data row of sheet based on header mappings...

updateMappedRowsInPlace(sheet, sourceObject, mappedMatchHeaders, mappedUpdateHeaders [, optParams])

updateMappedRecordsInPlace updates key-value pairs in one or more records by looking for a match based on mapped join keys Identical to updateRecordsInPlace but using header mappings and makes updates to based on header mappings. Performance note: This is an expensive method. Iterates through all destination data and writes each updates to the sheet individually. Arguments:

Parameters:
Name Type Argument Description
sheet Sheet

the GAS Sheet Object where the data will be written.
sourceObject Object

a single Object containing the key-value pairs to be used for updating matching records
mappedMatchHeaders Object

to be used for determining matching records, a plain object that maps one or more keys from the destination sheet onto source data keys
mappedUpdateHeaders Object

to be used for determining the update mask to be applied, and to specify mappings, a plain object that maps keys from destination sheet onto source keys
optParams Object <optional>

optional config parameters

Properties
Name Type Argument Description
headersRange Range <optional>

used to specify which range in the destionation sheet holds the headers. Expects a GAS Range object.
requireUnique Boolean <optional>

T/F whether or not to throw an error if query of destination data results in multiple matches
Returns:

updateStatus

Type
updateStatus

updateMappedRowsData(sheet, objects, mappedMatchHeaders, mappedUpdateHeaders, optParams)

updateMappedRowsData updates a sheet containing existing records with objects defined in the objects Array. Identical to updateMappedRowsData but using header mappings Requires that both source and destination data be unique based on mapped match headers Optionally inserts records from source if not found in destination. Optionally removes records in destination if not found in source. Requires that destination data have a unique key or key combination.

Parameters:
Name Type Description
sheet Sheet

the Sheet object where the data will be written
objects Array.<Object>

an Array of Objects, each of which contains data for a row
mappedMatchHeaders Object

to be used for determining matching records, a plain object that maps one or more keys from the destination sheet onto source data keys
mappedUpdateHeaders Object

to be used for determining the update mask to be applied, and to specify mappings, a plain object that maps keys from destination sheet onto source keys
optParams Object

optional config parameters:

Properties
Name Type Description
upsertNewRecords: Boolean

a Boolean, whether or not to insert records from source if not found in destination
removeAndArchiveNonMatchingRecords: Boolean

a Boolean, whether or not to remove data in non-matching rows and archive it in a separate "tabName_archive" tab.
headersRange: Range

a Range of cells where the column headers are defined. This defaults to the entire first row in sheet.
Returns:

updateStatus

Type
updateStatus

Type Definitions

Spreadsheet

Type:
  • Object

Sheet

Type:
  • Object

Range

Type:
  • Object

rowNum

Type:
  • number

updateStatus

Type:
  • Object
Properties:
Name Type Argument Description
recordsUpdated number <optional>

number of records updated
recordsInserted number <optional>

number of records inserted
recordsArchived number <optional>

number of records archived
error number <optional>

number of errors