Build Static HTML Sites from Google Sheets with Lambda and TiddlyWiki

In my last post, I went over how to create an AWS Lambda function without needing to code any Javascript. The function was used to generate static HTML pages from JSON, with the help of the low-code powerhouse TiddlyWiki. But how do we go about actually getting JSON into AWS and triggering the Lambda function to make our pages? In this guide, I’ll be generating static HTML from Google Sheets, with Lambda, API Gateway and TiddlyWiki.

Table of Contents

1. Generating Static HTML with Lambda (recap of previous post creating our Lambda function).
2. Generate JSON payloads from Gsheets webhooks.
3. Trigger Lambda with API Gateway and Body Mapping Templates.
4. Generate a static HTML page in Lambda from Gsheets.
5. Extra Credit – Add multiple commands to our Body Mapping Template.

Generating Static HTML with Lambda

To recap the last guide, consider the templated HTML list elements we created in our page.

<ul id="myUL">
`

<$list filter="[tag[Topic]]">
`<li><a href=`{{!!topic_link}}`>`{{!!title}}`</a></li>`
</$list>


`
</ul>

The purpose of this list is to make a sitemap of all of the Topics covered on the news website Purple News. In our Test Event in the Lambda console, we had dropped our data into the Lambda Wiki as follows.

{
  "commands": [
    "--aws",
    "s3-rendertiddler",
    "TopicPage",
    "us-east-1",
    "purplenewstestbucket",
    "TopicPage.html",
    "text/plain",
    "",
    "",
    "text/html"
  ],
  "tiddlers": [
    {
      "title": "Arizona",
      "topic_link": "/arizona",
      "tags": "Topic"
    },
  {
      "title": "Belarus",
      "topic_link": "/belarus",
      "tags": "Topic"
    }
  ]
}

The magic here is that all we need to send in a new Topic is one nice little JSON snippet to create a Tiddler:

 {
      "title": "Belarus",
      "topic_link": "/belarus",
      "tags": "Topic"
    }

In our case, each Tiddler will be represented as a list item on a single HTML page. (Note that we aren’t limited to creating just one HTML page… We could use the –rendertiddlers command to create a separate page for each Tiddler if we wanted.)

So, how do we get this neat JSON snippet into our Lambda function?

Generate JSON payloads from Gsheets webhooks

Before we can generate static html from Google sheets with Lambda, we need to figure out how to create our JSON.

Gsheets is always nice, because it’s quick and familiar and it has collaboration built-in. Importantly, it can also be setup to send out POST requests with JSON payloads.

Ultimately, it would be great if I could simply enter a topic and its associated link into a table, and have my HTML page automatically generated in S3.

title	topic_link
Arizona	/arizona
Belarus	/belarus

It turns out, it’s super easy to generate a webhook in Gsheets for this purpose. Webhooks are simply http requests which happen in response to an update to our data. The webhook will POST a JSON payload to an endpoint URL of our choice whenever we make a change. Ultimately this endpoint URL will point to API Gateway, and it will be connected to our Lambda function — but hold your horses, let’s tinker a little first.

1. Prepare a temporary endpoint for testing.

Before we dive into AWS API Gateway, let’s first create a simple endpoint for testing purposes. This will help us analyze the JSON that comes out of Gsheets.

I like Pipedream a lot, and I use it to test and build lots of APIs. Pipedream is comparable to Zapier, in that you can connect all types of SaaS services. You can also add js code in-between your steps, stringing together complex workflows.

After signing up for a Pipedream account, visit Workflows and create a New workflow.

Next, name your workflow and select “HTTP API” on the right.

You’ll notice that Pipedream will provide a URL where you can POST data and see what the payload looks like. Let’s leave this tab open since we’ll use it in our Gsheets script in the next step.

2. Create a simple webhook from Gsheets.

Now, let’s go ahead and start a new Google Sheet and enter the first couple of rows of topics. Then navigate to Tools >> Script Editor.

Once in the editor, pick a name for your script and paste the following into the editor. Thanks to Danny Hawkins for the script (I’ve only added a line for two http headers, which we’ll use with API gateway later). Remember to use your own URL from your Pipedream endpoint, and remember to hit Save.

function currentTime() {
 var d = new Date();
 var currentTime = d.toLocaleTimeString()
 return currentTime;
}
function CELL_CHANGED(row) {
 var options = {
 "method" : "post",
 "headers" : { "X-API-Key" : "mykey", "content-type" : "application/json" },
 "payload" : JSON.stringify(row)
 };
 
 UrlFetchApp.fetch('https://xXxXXxxXrqca7.m.pipedream.net', options);
 return "UpdatedAt: "+ currentTime()
}

To understand what’s happening here, the script is simply generating a POST request every time a row is updated (and the CELL_CHANGED function is called). This is how we will generate our static html from Google sheets with Lambda, since Lambda will receive the “payload” we “stringified” in the script.

All you have to do to now to trigger Sheets and send out some JSON is add a column in your row to call the script. You’ll see in the image all I did in Column C was:

=CELL_CHANGED(A2:B2)

3. Check Pipedream to see the JSON payload from Gsheets.

Flipping back to our Pipedream endpoint, you should be able to see the event that came in. Click on it in the left panel, then note the body of our payload in the right panel.

We know from earlier that we want our TiddlyWiki Lambda command to include a simple payload with “Belarus” and “/belarus” in its body, just like the body of this JSON payload. If you hover over the “0” under “body” in the event, you’ll see that Pipedream allows you to copy the path to this JSON object right in the tool. The path looks like this:

steps.trigger.event.body[0]

This is just path notation for JSON. It tells us that the info we want is in the first object (object 0) of the body object which came from our webhook event. We’ll see this body[0] business again soon enough in API Gateway.

We’ll also note the paths of the strings we actually want for our Tiddlers:

// title (Belarus)
steps.trigger.event.body[0][0]

// topic_link (/belarus) 
steps.trigger.event.body[0][1]

Trigger Lambda with API Gateway and Body Mapping Templates

Now that we know what JSON to expect from Gsheets, we can “map” the Gsheets outputs to the Lambda inputs that we’ll be needing to create our Tiddlers.

To do this, we’ll be using API Gateway’s Body Mapping Templates. In a world where complex JSON is flying around and hitting endpoints all the time, Body Mapping Templates are a really great tool to learn. All Body Mapping Templates do is help us filter out unwanted data from our payloads, and map the data we actually want to the format our function expects. This can be really helpful because it reduces (or eliminates) the need to do a whole bunch of JSON parsing in the Lambda function itself.

1. Let’s go ahead and create a new API endpoint, this time in AWS API Gateway.

Login to the AWS console and navigate to API Gateway.

Click “Create API” in the top right to create a new API.

Instead of the newer “HTTP APIs,” we’re going to stick with the traditional REST API. Click “Build.”

Then make your settings look like the image below, and click “Create API.”

You’ll notice that we now have a blank API with no “Resources.” Resources are our endpoints that we can create to “do stuff” when we ping them. So, for example here, we are going to create a resource called “newtopics” for receiving new topics from Gsheets.

Next, name the resource. Note that I am not selecting proxy resource or CORS here. We’re going to need CORS, but I’ve found that it is better to configure this differently to protect your sanity. Click Create Resource again.

2. Create a POST Method under our newtopics Resource.

Next, we’ll be presented with a blank panel under the “newtopics” resource, since we have no “Methods” yet. Here we want to create a POST Method, so that we can POST data from Gsheets to our endpoint, and get the data neatly shuttled over to our Lambda function.

Click Actions >> Create Method. Then fill out the form like pictured below.

If you followed along with the previous tutorial on creating the TiddlyWiki Lambda function, you’ll have the tw-topicbrowser function available when you start typing. Click Save.

3. Transform data coming in with a Body Mapping Template.

After clicking Save, you’ll get a popup letting you know you’re creating the resource. Then you’ll land on this nifty workflow screen. It shows how your data will traverse your endpoint, hit your function, then return something back. This is pretty cool because it gives us the opportunity to transform our data going in and going out, right here in the API Gateway console. This can be really useful in microservice architectures, because it allows us to keep our actual Lambda function as generic as possible, reusing one Lambda function for multiple resources/APIs.

In order to transform our Gsheets JSON payload to the neat data we want for our TiddlyWiki function, we’ll create a Body Mapping Template inside of the Integration Request. You can go ahead and click the hyperlink for Integration Request now.

Next, scroll all the way to the bottom of the screen on the Integration Request, and note the section for “Mapping Templates.” Here, we want to select “Never,” which means never pass-through our data as-is; we only want data to go through utilizing our Mapping Template. Since we haven’t created a Mapping Template yet, we’ll need to type “application/json” in the field and click the checkmark. This just means it’s a JSON template we’ll be creating.

After you click the check mark, the text “application/json” will become a hyperlink, which you can click to be presented with an editor to create your Mapping Template. Paste the following in as your Template. Note how we get our $inputRoot path from our test exercise with Pipedream earlier.

There’s one small trick here. In theory we would access the data we need in the Gsheets payload as follows:

"title": "$inputRoot.body[0][0]",
"topic_link": "$inputRoot.body[0][1]"

However, when we set up our Lambda in the previous tutorial, we created an Environment Variable, so that every time Lambda gets an “event,” it immediately accesses the “event.body” object. All this means here is that we can omit the word “body” in our template, since the Lambda already knows we want the body object when it gets a trigger event.

Your Mapping Template should look as follows:

## Set the variable inputRoot to the root JSON document
#set($inputRoot = $input.path('$')) 

{
  "commands": [
    "--aws",
    "s3-rendertiddler",
    "TopicPage",
    "us-east-1",
    "purplenewstestbucket",
    "TopicPage.html",
    "text/plain",
    "",
    "",
    "text/html"
  ],
  "tiddlers": [
    {
      "title": "$inputRoot[0][0]",
      "topic_link": "$inputRoot[0][1]",
      "tags": "Topic"
    }
  ]
}

4. Set up CORS support for our Resource by creating an OPTIONS Method.

Next, we need to make sure that our API can be accessed from other “origins” outside of the AWS domain. API Gateway provides a couple places in the GUI to do this automatically… but since Stackoverflow boards (and my nightmares) are full of examples of times this didn’t actually work, I like to set it up manually.

To start, we need to create one more Method under our newtopics Resource. The OPTIONS method is used to handle what’s called a “preflight request.” Whenever a request is sent to our POST method, browsers and other tools use the preflight request as a sort of handshake, to make sure that it’s ok to actually send the POST.

Back in our Resources screen, click Actions >> Create Method to create a new Method under the newtopics Resource. In the dropdown, selection “OPTIONS.”

Once it’s created, you’ll be presented with a creation page, where you should select “Mock.” Click Save.

5. Add required Headers to the OPTIONS Method.

Once created, click on the OPTIONS Method to see its workflow, and navigate to its Method Response page.

In the Method Response, create the headers as pictured below. It’s important to create these carefully. Match the cases, too.

If you’ve made it this far, you are awesome, so I have pasted the headers below to make your life easy.

X-Requested-With

Access-Control-Allow-Headers

Access-Control-Allow-Origin

Access-Control-Allow-Methods

Content-Type

Next, navigate back to the OPTIONS workflow page and visit the Integration Response page. Here, we’ll set the values of the required headers. Once again, be careful here. I’ve pasted the values again below the screenshot for your convenience. Remember to click Save when you’re done.

'*'

'Content-Type,X-Amz-Date,Authorization,X-Api-Key,X-Amz-Security-Token'

'*'

'OPTIONS,POST'

'application/json'

6. You’re a champ. Let’s deploy your API.

After you click Deploy API, a dialogue will pop up where you can set a deployment stage. This is simply for tracking changes and, if you want, deploying multiple versions of the API. I created a new stage called “stage1.” Then click Deploy.

Finally, API Gateway will provide your endpoint. It’s the URL right there on the page after you deploy.

7. Add an API Key for security.

Since I’ve decided to allow POSTs from all origins, not just Google, I’m going to add an API Key for security.

Navigate to the “API Keys” section in the left pane of the API Gateway console. On top, click Actions >> Create API Keys.

Name your key whatever you’d like (I’m going with apikey1) and select Auto Generate.

Next, back in the API workflow, select the POST Method and the Method Request.

Next, toggle the “API Key Required” to “true.”

Now your API Method will only work when your API Key is provided in the header X-API-Key. One more quick step is to associate the apikey1 we created with this API.

To finalize the key, navigate to “Usage Plans” in the left pane. Then click “Create” in the top. Here we get the opportunity to limit how often our API is called. It’s not super important in this use-case, but it can be really helpful on some of your other projects.

Once we click Next, we get the opportunity to associate this plan with our API and our Stage.

Lastly here, let’s associate our API Key with this usage plan. Navigate back to API Keys in the left pane, then select your apikey1. Then start keying in your usage plan, and click the check mark to save it. Remember to “Show” your key and copy it, so you can paste it in your Gsheets script in a moment.

You did it! You just created a handy API in AWS API Gateway. You can now accept POST requests from any origin (as long as you include your API Key in your headers). One thing — don’t forget to do Actions >> Deploy API again, to deploy with your API keys set!

Generate a static HTML page in Lambda from Google Sheets

We made it! Now that our API endpoint is ready, we are ready to generate our static html from Google sheets with our Lambda function. We can now go back to our Gsheets script and update our endpoint and our API Key. (To find your API key, navigate to API Keys >> apikey1 and click “Show.”)

Here’s my updated Gsheets script. Remember to use your own API Key and your own endpoint root URL. Also remember to click the save button after you make your changes.

function currentTime() {
 var d = new Date();
 var currentTime = d.toLocaleTimeString()
 return currentTime;
}
function CELL_CHANGED(row) {
 var options = {
 "method" : "post",
 "headers" : { "X-API-Key" : "txxxxxxxxxxxxxxxxxxxxxxxix", "content-type" : "application/json" },
 "payload" : JSON.stringify(row)
 };
 
 UrlFetchApp.fetch('https://xxxxxxxxxx.execute-api.us-east-1.amazonaws.com/stage1/newtopics', options);
 return "UpdatedAt: "+ currentTime()
}

Now, when we update a row in our Gsheet and call our webhook script, the row of data will make it all the way to Lambda — and generate our page!

To verify our work, we can visit S3 and download the page. Sweet!

Extra Credit – Add multiple commands to our Body Mapping Template

The process above is great for generating static pages in S3 with Google sheets. In fact, by using the “–rendertiddlers” command instead of the “–rendertiddler” command, we can generate many pages with this setup effortlessly.

There’s only one issue with our work today: since we are only generating one page here, our current command will overwrite our page every time we send in a row of data. This creates an issue with our current use case, because we want to add data incrementally and grow the list. So how can we make the Lambda “remember” our previous row submissions?

Thanks to the magic of TiddlyWiki, and its roots as a self-contained single file, we can do this simply by modifying our commands in our Body Mapping Template. What we’ll do is store the submissions in a separate version of our Wiki, which will be saved to S3 as a larger HTML file. Then, when we send data from Gsheets, we’ll first load the previous Tiddlers from this HTML file, then generate our sitemap once those Tiddlers (and the new Topic Tiddler we send in) is loaded.

Here’s how it works.

1. Create a self-contained HTML version of our Wiki in S3.

Currently, our Wiki is stored as an index.js file — it’s our actual Lambda function. We set this up in the previous tutorial. But the index.js is immutable: we don’t add data to the index.js itself when we pass in new tiddlers. To create storage for our Lambda, we’ll create an HTML version of the full Wiki in S3, and store our Tiddler topics there.

Back in the Lambda console, let’s create a new Test event for the purpose of generating a full HTML representation of our Wiki. Call the Test event “renderwholething.” Below is the JSON for the new Test event.

{
  "commands": [
    "--aws",
    "s3-rendertiddler",
    "$:/core/save/all",
    "us-east-1",
    "purplenewstestbucket",
    "topicbrowser_store.html",
    "text/plain",
    "",
    "",
    "text/html"
  ],
  "tiddlers": [
    {
      "title": "France",
      "topic_link": "/france",
      "tags": "Topic"
    }
  ]
}

You’ll see in the Test event above that we’re using the “$:/core/save/all” render template, which is built-in to TiddlyWiki. This will render a functioning single-file TiddlyWiki. We won’t even need to open that HTML file in our browser; we’re just using it to store our Tiddlers as they come in.

Next, go ahead and run the Test event “renderwholething.”

In S3, we should now have two files: our Topic.html from our previous work, and our new topicbrowser_store.html for storing our Tiddler submissions. Note the size of the files. Topic.html should be tiny, since it’s a mean and lean HTML template for displaying our sitemap on a live site; the topicbrowser_store.html file should be between 2MB – 3MB, which is the size of an empty version of a full TiddlyWiki HTML file.

Now that we have an HTML wiki setup to store our topics, we can amend our TiddlyWiki command in API Gateway to utilize the store.

The process is like this–

Step 1 – Load the store, which includes previous Topics submitted:

"--aws",
    "s3-load",
    "us-east-1",
    "purplenewstestbucket",
    "topicbrowser_store.html",

Step 2 – render our TopicPage.html like before, since now we’ve loaded all the previous Topic Tiddlers:

"--aws",
    "s3-rendertiddler",
    "TopicPage",
    "us-east-1",
    "purplenewstestbucket",
    "TopicPage.html",
    "text/plain",
    "",
    "",
    "text/html",

Step 3 – save down the store again, which will include the new Topic we threw in with the rest of our command:

"--aws",
    "s3-rendertiddler",
    "$:/core/save/all",
    "us-east-1",
    "purplenewstestbucket",
    "topicbrowser_store.html",
    "text/plain",
    "",
    "",
    "text/html"

So, to update our Body Mapping Template, the final Template should look as follows. When you update your Body Mapping Template, don’t forget to deploy your API again.

## Set the variable inputRoot to the root JSON document
#set($inputRoot = $input.path('$')) 

{
  "commands": [
    "--aws",
    "s3-load",
    "us-east-1",
    "purplenewstestbucket",
    "topicbrowser_store.html",
    "--aws",
    "s3-rendertiddler",
    "TopicPage",
    "us-east-1",
    "purplenewstestbucket",
    "TopicPage.html",
    "text/plain",
    "",
    "",
    "text/html",
    "--aws",
    "s3-rendertiddler",
    "$:/core/save/all",
    "us-east-1",
    "purplenewstestbucket",
    "topicbrowser_store.html",
    "text/plain",
    "",
    "",
    "text/html"
  ],
  "tiddlers": [
    {
      "title": "$inputRoot[0][0]",
      "topic_link": "$inputRoot[0][1]",
      "tags": "Topic"
    }
  ]
}

We can replace our Mapping Template with the above, and our Lambda will remember previous submissions. Now we can build our sitemap over time as we add topics.

Let’s check our TopicPage.html to verify.

Success!

---

If you liked this post, follow me on Twitter @philwonski so you know when I post similar content. There’s also lots of great (and less esoteric) content being produced by our team on a regular basis — be sure to find us on LinkedIn, and sign up to our newsletter in the footer of this page.

Featured Photo by Kristian Strand on Unsplash