Sheets-based Computer Aided Software Development (CASD) #1
Tags: programming, googlesheets, googleappsscript, javascript
Published: 2019-08-07 07:24:32

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](https://docs.microsoft.com/en-us/windows/win32/midl/com-dcom-and-type-libraries) compiler leaving a [typelib](http://resources.esri.com/help/9.3/arcgisdesktop/com/COM/ExtendAO/TypeLibrariesAndIDL.htm) 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 ![](https://thepracticaldev.s3.amazonaws.com/i/55yj6xkr3z0tw95uarqr.png)

Below that, in cell C28, I have the following formula:
```
=GenerateSettings(C2:C26,A2:B26)
```
which generates the following:
```javascript
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.
```javascript
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, ' ');
}
```