I used to use spreadsheets a lot to generate programming assets, particularly using Excel plus macros to generate Interface Description Language files for DLL files. These files were then compiled using Microsoft's MIDL compiler leaving a typelib file that could then be registered and the symbols used as if the DLL was a real COM DLL.
Using the spreadsheet saved a ton of time. I could specify each function and each parameter and have the macros cook up the relevant IDL code. Then I could copy and paste it into a text editor, clean it up a bit, and then compile to .TLB and so on through the process.
So after a long hiatus I'm doing the same again, this time generating JSON configuration objects from a Google Sheets file. In this case, column A contains the name of the field, column B the data type, and columns C and beyond the configuration data for each of the files/functions that will use the data.
For example, here's a sample A:C 
Below that, in cell C28, I have the following formula:
=GenerateSettings(C2:C26,A2:B26)
which generates the following:
var settings = {
"activateSourceSheetOnExit": false,
"activeTargetSheetOnExit": false,
"cargo": {},
"checkboxOffset": -1,
"checkboxColumn": "",
"clearData": true,
"clearFormat": true,
"clearHeads": true,
"dataColumns": [],
"dataStartLine": -1,
"finalTasks": [],
"headsRow": -1,
"ignoredColumns": [],
"ignoreHeaderlessColumns": false,
"includeSidebar": false,
"querySelector": {
"sid": "REST_function_name",
"p1": "REST_parameter_value",
"p2": "6,7,8,9,10"
},
"removeCheckedLines": false,
"resetCheckbox": false,
"sourceName": "",
"sourceRange": "",
"targetName": "Main",
"targetRange": "A:J"
}
GenerateSettings is below. You'll see artifacts indicating ts2gas's conversion of my TypeScript to ES3, the dialect used in Google Apps Script.
You may want to check the references on the extra parameters you can give to JSON.stringify() (below as JSON.stringify(settings, null, ' ')) as they do improve readability for humans.
function GenerateSettings(valueRange, settingsRange) {
var settings = {};
var settingsValues = settingsRange; //.getValues();
var offset = 0;
for (var _i = 0, valueRange_1 = valueRange; _i < valueRange_1.length; _i++) {
var cell = valueRange_1[_i];
var nameType = settingsValues[offset];
var name = nameType[0];
var type = nameType[1];
var cellValue = cell[0];
offset++;
var formattedCellValue = void 0;
switch (type) {
case "object":
formattedCellValue = cellValue === "" ? '{}' : cellValue;
break;
case "object[]":
case "number[]":
formattedCellValue = cellValue === "" ? '[]' : '[' + cellValue.split(/,\s*/g).join(",") + ']';
break;
case "string[]":
formattedCellValue = cellValue === "" ? '[]' : '[' + cellValue.split(/,\s*/g).map(function (elt) { return "'" + elt + "'"; }).join(",") + ']';
break;
case "number":
formattedCellValue = cellValue === "" ? -1 : cellValue;
break;
case "boolean":
formattedCellValue = cellValue === "" ? 'false' : (cellValue ? 'true' : 'false');
break;
case "literal":
formattedCellValue = cellValue === "" ? '""' : cellValue; // was eval
break;
case "string":
formattedCellValue = cellValue === "" ? '""' : '"' + cellValue + '"';
break;
}
name = "['" + name.split(/\./g).join("']['") + "']";
var js = "if (!settings) settings = {}; settings" + name + "=" + formattedCellValue + ";";
eval(js);
}
return "var settings = " + JSON.stringify(settings, null, ' ');
}
Top comments (0)