Sheets-Based CASD #2

As you may recall from posting #1, this is all being done in the context of Google Sheets and Google Apps Script.

In the earlier posting, we created a spreadsheet which described a JSON object's structure, with names and types. Then we made columns for each of the instances of that structure. We generated the JSON using a user-defined function and displayed it in a cell whence it could be copied.

Well I got tired of that last step because it also comes out of that cell with double-quotes around it, and every double-quote inside gets doubled. So one goes from spreadsheet to text editor to destination.

So what if one could transmit the result somewhere so that this quoting nightmare could be avoided? Wouldn't it be better to be able to just open a file and copy the text? (Wellll.... a button which could copy to clipboard would be cool. Maybe next time.)

SERVER END

We have a couple of Azure servers. On one of them, in a place where we keep the majority of our .ashx handlers, I've put the following

<%@ WebHandler Language="C#" Class="SettingsMaker.Handler" %>
using System;
using System.Web;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Net;
using System.Diagnostics;
using System.IO;

namespace SettingsMaker
{
    public class Handler : IHttpHandler
    {
        private readonly System.Object threadLock = new System.Object();

        public void ProcessRequest(HttpContext context)
        {
            string fn = context.Request.QueryString["fn"];
            string postBody = string.Empty;
            using (var reader = new System.IO.StreamReader(context.Request.InputStream, System.Text.Encoding.UTF8))
            {
                postBody = reader.ReadToEnd();
            }

            lock (threadLock)
            {
                var target = @"C:\temp\Settings";
                System.IO.Directory.CreateDirectory(target);
                File.WriteAllText(Path.Combine(target, fn + ".json"), postBody);
            }

            context.Response.ContentType = "text/json";
            context.Response.Write("{}");
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

Probably some of those using statements are redundant. Anyway, you get the idea: there's an ASP Handler waiting for me to sent code to and it's going to save everything in c:\temp\Settings. The name of the file is going to be in the fn variable and the text is going to be in the POST body. Every time it runs it'll overwrite what's there, and the lock is an attempt to keep it from trying to write when another instance is writing to the same file.

CLIENT END

Okay, now the Google Sheets end. The spreadsheet image I posted is largely the same except that we now have a row for the functionName which we take from the top row of the column. That becomes the name of the file on the server.

The source is in TypeScript.

function GenerateSettings(valueRange: any[], settingsRange: any[]) {
  const settings: any = {};
  const settingsValues = settingsRange; //.getValues();
  let offset = 0;
  let sj;
  for (const cell of valueRange) {
    const nameType = settingsValues[offset];
    let name = nameType[0];
    const type = nameType[1];
    const cellValue = cell[0];
    offset++;
    let formattedCellValue: any;
    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((elt: string) => "'" + 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("']['") + "']";
    let js = "if (!settings) settings = {}; settings" + name + "=" + formattedCellValue + ";";
    eval(js);
  }
  const result = "var settings = " + JSON.stringify(settings, null, ' ');
  SettingsMaker(settings.functionName, result);
  return result;
}

function SettingsMaker(name: string, body: string): void {
  const f = UrlFetchApp.fetch("http://some.server.somewhere/SettingsMaker.ashx?fn=" + name, {
    'method': 'post',
    'muteHttpExceptions': true,
    'payload': body
  });
}

Note that it's the call to SettingsMaker() that transmits the generated code, the second parameter to the UrlFetchApp.fetch call being what defines the method and the payload.

Now because I have an RDP session open into the server, I can navigate to c:\temp\Settings, open the relevant .json file and copy straight into my project without having to deal with doubled double-quotes and the like.

There may be other ways of doing this, but this is working okay for now.