// Includes functions for exporting active sheet or all sheets as JSON object (also Python object syntax compatible).
// Tweak the makePrettyJSON_ function to customize what kind of JSON to export.
var FORMAT_ONELINE = "One-line";
var FORMAT_MULTILINE = "Multi-line";
var FORMAT_PRETTY = "Pretty";
var LANGUAGE_JS = "JavaScript";
var LANGUAGE_PYTHON = "Python";
var STRUCTURE_LIST = "List";
var STRUCTURE_HASH = 'Hash (keyed by "id" column)';
/* Defaults for this particular spreadsheet, change as desired */
var DEFAULT_FORMAT = FORMAT_PRETTY;
var DEFAULT_LANGUAGE = LANGUAGE_JS;
var DEFAULT_STRUCTURE = STRUCTURE_LIST;
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [
{ name: "Export JSON for this sheet", functionName: "exportSheet" },
// { name: "Export JSON for all sheets", functionName: "exportAllSheets" },
];
ss.addMenu("Export JSON", menuEntries);
}
function makeLabel(app, text, id) {
var lb = app.createLabel(text);
if (id) lb.setId(id);
return lb;
}
function makeListBox(app, name, items) {
var listBox = app.createListBox().setId(name).setName(name);
listBox.setVisibleItemCount(1);
var cache = CacheService.getPublicCache();
var selectedValue = cache.get(name);
Logger.log(selectedValue);
for (var i = 0; i < items.length; i++) {
listBox.addItem(items[i]);
if (items[1] == selectedValue) {
listBox.setSelectedIndex(i);
}
}
return listBox;
}
function makeButton(app, parent, name, callback) {
var button = app.createButton(name);
app.add(button);
var handler = app
.createServerClickHandler(callback)
.addCallbackElement(parent);
button.addClickHandler(handler);
return button;
}
function makeTextBox(app, name) {
var textArea = app
.createTextArea()
.setWidth("100%")
.setHeight("200px")
.setId(name)
.setName(name);
return textArea;
}
function exportAllSheets(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var sheetsData = {};
for (var i = 0; i < sheets.length; i++) {
var sheet = sheets[i];
var rowsData = getRowsData_(sheet, getExportOptions(e));
var sheetName = sheet.getName();
sheetsData[sheetName] = rowsData;
}
var json = makeJSON_(sheetsData, getExportOptions(e));
displayText_(json);
}
function exportSheet(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var rowsData = getRowsData_(sheet, getExportOptions(e));
var json = makeJSON_(rowsData, getExportOptions(e));
displayText_(json);
}
function getExportOptions(e) {
var options = {};
options.language = (e && e.parameter.language) || DEFAULT_LANGUAGE;
options.format = (e && e.parameter.format) || DEFAULT_FORMAT;
options.structure = (e && e.parameter.structure) || DEFAULT_STRUCTURE;
var cache = CacheService.getPublicCache();
cache.put("language", options.language);
cache.put("format", options.format);
cache.put("structure", options.structure);
Logger.log(options);
return options;
}
function makeJSON_(object, options) {
if (options.format == FORMAT_PRETTY) {
var jsonString = JSON.stringify(object, null, 4);
} else if (options.format == FORMAT_MULTILINE) {
var jsonString = Utilities.jsonStringify(object);
jsonString = jsonString.replace(/},/gi, "},\n");
jsonString = prettyJSON.replace(/":\[{"/gi, '":\n[{"');
jsonString = prettyJSON.replace(/}\],/gi, "}],\n");
} else {
var jsonString = Utilities.jsonStringify(object);
}
if (options.language == LANGUAGE_PYTHON) {
// add unicode markers
jsonString = jsonString.replace(/"([a-zA-Z]*)":\s+"/gi, '"$1": u"');
}
return jsonString;
}
function displayText_(text) {
var file = DriveApp.createFile(
`${
Date.now().toString() + "#" + Math.random().toString(36).substr(2, 9)
}.txt`,
text
);
var url = file.getUrl();
var output = HtmlService.createHtmlOutput(
`<span>Download: ${url}</span> <textarea style='width:100%;' rows='20'>${text}</textarea>`
);
output.setWidth(400);
output.setHeight(300);
SpreadsheetApp.getUi().showModalDialog(output, "Exported JSON");
}
// 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, options) {
var headersRange = sheet.getRange(
1,
1,
sheet.getFrozenRows(),
sheet.getMaxColumns()
);
var headers = headersRange.getValues()[0];
var dataRange = sheet.getRange(
sheet.getFrozenRows() + 1,
1,
sheet.getMaxRows(),
sheet.getMaxColumns()
);
var objects = getObjects_(dataRange.getValues(), normalizeHeaders_(headers));
if (options.structure == STRUCTURE_HASH) {
var objectsById = {};
objects.forEach(function (object) {
objectsById[object.id] = object;
});
return objectsById;
} else {
return objects;
}
}
// 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
/*
Indexes (j) - fields name:
0 - asins
1 - brand
2 - brandLink
3 - categories
4 - colors
5 - dateAdded
6 - dateUpdated
7 - descriptions
8 - dimension
9 - dontFetchAmazonOptions
10 - features
11 - groupId
12 - imageURLs
13 - isbn
14 - manufacturer
15 - manufacturerLink
16 - merchants
17 - merchantLink
18 - mostRecentPriceAmount
19 - mostRecentPriceCurrency
20 - name
21 - primaryImageURLs
22 - sizes
23 - waivio_options
24 - waivio_tags
25 - weight
26 - emails
27 - latitude (only for restaurant)
28 - longitude (only for restaurant)
29 - address (only for restaurant)
30 - city (only for restaurant)
31 - province (only for restaurant)
32 - postalCode (only for restaurant)
33 - country (only for restaurant)
*/
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];
//var cellDataArray = [data[i][j]];
if (isCellEmpty_(cellData)) {
continue;
} else if (
j === 0 ||
j === 1 ||
j === 2 ||
j === 5 ||
j === 6 ||
j === 8 ||
j === 9 ||
j === 11 ||
j === 13 ||
j === 14 ||
j === 15 ||
j === 17 ||
j === 18 ||
j === 19 ||
j === 20 ||
j === 25 ||
j === 27 ||
j === 28 ||
j === 29 ||
j === 30 ||
j === 31 ||
j === 32 ||
j === 33
) {
// string fields
object[keys[j]] = cellData;
} else if (
cellData.toString().includes(";") &&
(j === 3 || j === 12 || j === 21 || j === 22 || j === 4 || j === 26)
) {
// array fields
object[keys[j]] = cellData
.toString()
.split(";")
.map((item) => item.trim());
} else if (j === 10) {
var flagValueArray = true;
object[keys[j]] = processString_(cellData, flagValueArray);
} else if (j === 23 || j === 24) {
object[keys[j]] = processString_(cellData);
} else if (j === 7) {
object[keys[j]] = [{value: cellData}];
} else if (j === 16) {
object[keys[j]] = [{name: cellData}];
}
else {
object[keys[j]] = [cellData];
}
hasData = true;
}
if (hasData) {
objects.push(object);
}
}
return objects;
}
function processString_(str, valueArray) {
var result = [];
var urlPattern = /(https?:\/\/[^\s]+)/g; // Regular expression to find URLs
// Function to handle the URL case
function handleUrl(obj, key, value) {
if (key.trim().toLowerCase() === 'image') {
var match = value.match(urlPattern);
if (match) {
obj[key.trim()] = match[0];
return true;
}
}
return false;
}
var parts = str.split("*");
for (var i = 0; i < parts.length; i++) {
var obj = {};
var subparts = parts[i].split(";");
for (var j = 0; j < subparts.length; j++) {
if (subparts[j].indexOf(":") !== -1) {
var subsubparts = subparts[j].split(":");
// Check if the key-value pair is a URL and handle it
if (!handleUrl(obj, subsubparts[0], subsubparts.slice(1).join(':'))) {
var value = subsubparts.slice(1).join(':').trim();
if (Object.keys(obj).length % 2 !== 0) {
if (valueArray) {
value = [value];
}
value = value;
}
obj[subsubparts[0].trim()] = value;
}
}
}
if (Object.keys(obj).length) result.push(obj);
}
if (!result.length) return "Invalid String";
return result;
}
// 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;
return header;
}
// 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;
}