Skip to content

Instantly share code, notes, and snippets.

@dbieber
Created October 21, 2018 18:57
Show Gist options
  • Save dbieber/471a3103adb727d6985892338de00aea to your computer and use it in GitHub Desktop.
Save dbieber/471a3103adb727d6985892338de00aea to your computer and use it in GitHub Desktop.
The source of SpreadsheetDB used in the "Writing a Mail Merge in Google Apps Script" post on blog.davidbieber.com
function getColumnIndexes(columns) {
// Returns a map from column name to column index.
var container = {};
for (var i = 0; i < columns.length; i++) {
var value = columns[i];
container[value] = i;
}
return container;
}
function getColumnIndexesFromSheet(sheet) {
// Returns a map from column name to column index.
var columnNames = getColumnNames(sheet);
return getColumnIndexes(columnNames);
}
// Spreadsheet utilities
function getSheetByName(spreadsheet, name) {
// Return a sheet from the provided spreadsheet with the given name.
// Use SpreadsheetApp to get a spreadsheet.
return spreadsheet.getSheetByName(name);
}
function getColumnNames(sheet) {
// Gets the column names from the provided sheet.
return sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
}
function getRowWithColumnValue(sheet, columnIndex, value) {
// Gets the values corresponding to the last row with columnIndex matching value.
return getRowRangeWithColumnValue(sheet, columnIndex, value).getValues()[0];
}
function getRowRangeWithColumnValue(sheet, columnIndex, value) {
// Gets the Range corresponding to the last row with columnIndex matching value.
var condition = function(x) { return x == value; };
return getRowRangeWithColumnSatisfying(sheet, columnIndex, condition);
}
function getRowRangeWithColumnSatisfying(sheet, columnIndex, condition) {
// Gets the Range corresponding to the last row with columnIndex satisfying the condition function.
var firstRow = 2;
// Get a single column of values.
var values = sheet.getRange(firstRow, columnIndex + 1, sheet.getLastRow()).getValues();
for (var i = values.length - 1; i >= 0; i--) {
var row = values[i];
if (condition(row[0])) {
return getRowRange(sheet, i + firstRow);
}
}
}
function updateRowWithColumnValue(sheet, columnIndex, value, newColumnIndex, newValue) {
// Updates the last row with columnIndex matching value to use the newValue in newColumnIndex.
var range = getRowRangeWithColumnValue(sheet, columnIndex, value);
var values = range.getValues();
values[0][newColumnIndex] = newValue;
range.setValues(values);
}
function updateRowWithColumnSatisfying(sheet, columnIndex, condition, newColumnIndex, newValue) {
// Updates the last row with columnIndex satisfying the condition fn to use the newValue in newColumnIndex.
var range = getRowRangeWithColumnSatisfying(sheet, columnIndex, condition);
var values = range.getValues();
values[0][newColumnIndex] = newValue;
range.setValues(values);
}
function forEachRow(sheet, doFn) {
// Perform the doFn for each row in the spreadsheet.
return forEachRowSatisfying(sheet, function(row) { return true; }, doFn);
}
function forEachRowSatisfying(sheet, condition, doFn) {
// Perform the doFn for each row in the spreadsheet satisfying the condition fn.
var firstRow = 2;
// Get a single column of values.
var range = sheet.getRange(/*row=*/firstRow, /*column=*/1,
/*numRows=*/sheet.getLastRow() - firstRow + 1, /*numColumns=*/sheet.getLastColumn());
var values = range.getValues();
var update = false;
for (var i = values.length - 1; i >= 0; i--) {
var row = values[i];
if (condition(row)) {
Logger.log(i);
var newRow = doFn(row);
if (newRow) {
update = true;
values[i] = newRow;
}
}
}
if (update) {
range.setValues(values);
}
}
function randomRow(sheet) {
var rowNumber = randint(2, sheet.getLastRow());
return getRow(sheet, rowNumber);
}
function getRow(sheet, row) {
return getRowRange(sheet, row).getValues()[0];
}
function getRowRange(sheet, row) {
return sheet.getRange(row, 1, /*numRows=*/1, /*numColumns=*/sheet.getLastColumn());
}
function addRow(sheet, row) {
sheet.appendRow(row);
}
function randint(min, max) {
// inclusive
return Math.floor(Math.random() * (max - min + 1)) + min;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment