Skip to content

Instantly share code, notes, and snippets.

@dangtrinhnt
Created September 25, 2015 09:11
Show Gist options
  • Save dangtrinhnt/e041557101dac07bbdae to your computer and use it in GitHub Desktop.
Save dangtrinhnt/e041557101dac07bbdae to your computer and use it in GitHub Desktop.
Google Spreadsheet to dictionary in Google Apps Script
// dictionary of columns (list)
// thedict = {'<col_header0>': ['<row0>','<row1>',...], '<col_header1>': ['<row0', '<row1>',...]...}
function sheet_to_dict(spreadsheetid, sheetname) {
var result_dict = {};
var dataspreadsheet = SpreadsheetApp.openById(spreadsheetid);
var sheet = dataspreadsheet.getSheetByName(sheetname);
var cols = sheet.getLastColumn();
var rows = sheet.getLastRow();
var data = sheet.getDataRange().getValues();
// data[row][col]
for (var c = 0; c < cols; c++) {
var header = data[0][c];
var col_data_list = [];
for(var r = 1; r < rows; r++) {
var cell_data = data[r][c];
if(cell_data !== ''){
col_data_list.push(cell_data);
}
}
if (col_data_list.length > 0) {
result_dict[data[0][c]] = col_data_list;
}
}
return result_dict;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment