Friday, February 23, 2024
 Popular · Latest · Hot · Upcoming
92
rated 0 times [  93] [ 1]  / answers: 1 / hits: 8438  / 3 Years ago, thu, august 5, 2021, 12:00:00

I'm using this script to export Google Sheets as a JSON file and it works perfectly fine. However in my sheet im using names like this "qwe_qwe" and when I'm exporting its being formatted to "qweqwe". Is it possible somehow to export file in raw format.


I believe that "var FORMAT_PRETTY = 'Pretty';" makes the difference, however I can't make it work without this variable.


UPD: Found that this function if (!isAlnum_(letter)) checking for Alphanumeric chars, so I've decided to add smthng like this if (!isAlnum_(letter) && !isAlnum_("_")), however it doesn't work.




// 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 output = HtmlService.createHtmlOutput(<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
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;
}




More From » json

 Answers
5

Sample Sheet:


enter


Export to JSON (based on original code):


column headers were formatted using normalizeHeader_()


Output:


5:19:32 AM  Notice  Execution started
5:19:34 AM Info [
{
"columna": "A2",
"columnb": "B2",
"columnc": "C2"
},
{
"columna": "A_3",
"columnb": "B_3",
"columnc": "C_3"
}
]
5:19:34 AM Notice Execution completed



Export to JSON (raw format column header):


Change var objects = getObjects_(dataRange.getValues(), normalizeHeaders_(headers)); in getRowsData_() to var objects = getObjects_(dataRange.getValues(), headers);


Output:


5:20:04 AM  Notice  Execution started
5:20:06 AM Info [
{
"Column_A": "A2",
"Column_B": "B2",
"Column_C": "C2"
},
{
"Column_A": "A_3",
"Column_B": "B_3",
"Column_C": "C_3"
}
]
5:20:06 AM Notice Execution completed



Export to JSON (format column header but include '_')


Use (!isAlnum_(letter) && (letter != '_')) in normalizeHeader_()


Output:


5:28:11 AM  Notice  Execution started
5:28:13 AM Info [
{
"column_a": "A2",
"column_b": "B2",
"column_c": "C2"
},
{
"column_a": "A_3",
"column_b": "B_3",
"column_c": "C_3"
}
]
5:28:13 AM Notice Execution completed

[#1015] Thursday, July 29, 2021, 3 Years  [reply] [flag answer]
Only authorized users can answer the question. Please sign in first, or register a free account.
jamila

Total Points: 490
Total Questions: 94
Total Answers: 94

Location: Lebanon
Member since Sun, Aug 2, 2020
4 Years ago
;