I was deploying an app service in a new region today and ran into a quota issue. The error message was:

Error: creating Service Plan: (Serverfarm Name "***devplan" / Resource Group "***_dev"): web.AppServicePlansClient#CreateOrUpdate: Failure sending request: StatusCode=401 -- Original Error: Code="Unauthorized" Message="This region has quota of 0 instances for your subscription. Try selecting different region or SKU."

This was a pretty simple deployment to an S1 app service plan. I’ve run into this before and it’s typically easy to request a bump in quota in the subscription. My problem today was that it isn’t obvious what CPU quota I need to request. I Googled around and found some suggestion that S1 ran on A series VMs but that wasn’t something I had any limits on.

Creating in the UI gave the same error

I asked around and eventually somebody in the know was able to look into the consumption in that region. The cloud was full! Well not full but creation of some resources was restricted. Fortunately this was just a dev deployment so I was able to move to a different region and get things working. It would have been pretty miserable if this was a production deployment or if I was adding onto an existing deployment.

I was working on a deployment today and ran into an issue with a keyvault reference. In the app service the keyvault reference showed that it wasn’t able to get the secret. The reference seemed good but I wasn’t seeing what I wanted to see which was a couple of green checkmarks

The managed identity on the app service had only GET access to the keyvault. I added LIST access and the reference started working. I’m not sure why this is but I’m guessing that the reference is doing a LIST to get the secret and then a GET to get the secret value.

Officially comments are not supported in the JSON format. In fact this lack of ability to comment is one of the reasons that lead to the downfall of the JSON based project system during the rewrite of the .NET some years back. However they sure can be useful to support. In my case I wanted to add some comments to the body of a request to explain a parameter in Postman. I like to keep comments as close to the thing they describe as possible so I didn’t want this on a wiki somewhere nobody would ever find.

The content looked something like

{
    "data": {
        "form_data": {
            "effective_date": "2023-02-23",
            "match_on_per_pay_period_basis": 0, /* 0 if yes, 1 if no */
            "simple_or_tiered": 1, /* 0 if simple 1 if tiered */
        }
    }
}

This was going to an ExpressJS application which was parsing the body using body-parser. These days we can just use express.json() and avoid taking on that additional dependency. The JSON parsing in both these is too strict to allow for comments. Fortunately, we can use middleware to resolve the issue. There is a swell package called strip-json-comments which does the surprisingly difficult task of stripping comments. We can use that.

The typical json paring middleware looks like

app.use(express.json())

or 

app.use(bodyParser.json())

Instead we can do

import stripJsonComments from 'strip-json-comments';

...

app.use(express.text{
    type: "application/json" // 
}) //or app.use(bodyParser.text({type: "application/json}))
app.use((req,res,next)=> {
    if(req.body){
        req.body = stripJsonComments(req.body);
    }
    next();
})

This still allows us to take advantage of the compression and character encoding facilities in the original parser while also intercepting and cleaning up the JSON payload.

Excel is the king of spreadsheets and I often find myself in situation where I have to write our Excel files in an application. I’d say that as an application grows the probability of needing Excel import or export approaches 1. Fortunately, there are lots of libraries out there to help with Excel across just about every language. The quality and usefuleness of these libraries varies a lot. In Ruby land there seem to be a few options.

Spreadsheet

https://github.com/zdavatz/spreadsheet/

As the name suggests this library deals with Excel spreadsheets. It is able to both read and write them by using Spreadsheet::Excel Library and the ParseExcel Library. However it only supports the older XLS file format. While this is still widely used it is not the default format for Excel 2007 and later. I try to stay clear of the format as much as possible. There have not been any releases of this library in about 18 months but there haven’t been any releases of the XLS file format for decades so it doesn’t seem like a big deal.

The library can be installed using

gem install spreadsheet

Then you can use it like so

require 'spreadsheet'

workbook = Spreadsheet.open("test.xls")
worksheet = workbook.worksheet 0
worksheet.rows[1][1] = "Hello there!"
workbook.write("test2.xls")

There are some limitations around editing files such as cell formats not updating but for most things it should be fine.

RubyXL

https://github.com/weshatheleopard/rubyXL

This library works on the more modern XLSX file formats. It is able to read and write files with modifications. However there are some limitations such as being unable to insert images

require 'rubyXL'

  # only do this if you don't care about memory usage, otherwise you can load submodules separately
  # depending on what you need
require 'rubyXL/convenience_methods'

workbook = RubyXL::Parser.parse("test.xlsx")
worksheet = workbook[0]
cell = worksheet.cell_at('A1')
cell.change_contents("Hello there!")
workbook.write("test2.xlsx")

CAXLSX

https://github.com/caxlsx/caxlsx

This library is the community supported version of AXLSX. It is able to generate XLSX files but not read them or modify them. There is rich support for charts, images and other more advanced excel features. The

Install using

gem install caxlsx

And then a simple example looks like

require 'axlsx'

p = Axlsx::Package.new
workbook = p.workbook

wb.add_worksheet(name: 'Test') do |sheet|
  sheet.add_row ['Hello there!']
end

p.serialize "test.xlsx"

Of all the libraries mentioned here the documentation for this one is the best. It is also the most actively maintained. The examples directory https://github.com/caxlsx/caxlsx/tree/master/examples gives a plethora of examples of how to use the library.

Fast Excel

https://github.com/Paxa/fast_excel

This library focuses on being the fastest excel library for ruby. It is actually written in C to speed it up so comes with all the caveats about running native code. Similar to CAXLSX it is only able to read and write files and not modify them.

require 'fast_excel'

  # constant_memory: true streams changes to disk so it means that you cannot
  # modify an already written record
workbook = FastExcel.open("test.xlsx", constant_memory: true)
worksheet = workbook.add_worksheet("Test")

bold = workbook.bold_format
worksheet.set_column(0, 0, FastExcel::DEF_COL_WIDTH, bold)
worksheet << ["Hello World"]
workbook.close

As you can see here the library really excels at adding consistently shaped rows. You’re unlikely to get a complex spreadsheet with headers and footers built using this tooling.