Sat, 21 Mar 2015

Reference the Previous Sheet in Google Spreadsheets

I'm maintaining this soccer stats sheet and have different games on different sheets. To build up sums I like to reference data from the previous sheet. It took me some trial and error to get going. Essentially it requires a custom function and the use of INDIRECT.

For the custom function go to Tools > Script Editor and paste the following code

 * Retrieves a reference to the previous sheet, or null
 * input is unused.
function prevSheet(input) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = sheet.getSheets();
  var prev = null;
  for(var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetId() == sheet.getSheetId()) {
      return prev.getName();
    prev = sheets[i];
  return null;

The code is also available here as an add-on.

To use the function you need to use the INDIRECT function, for example to reference cell A2 on the previous sheet:

=INDIRECT("'" & prevSheet(GoogleClock()) & "'!A2")

To be able to copy the formula around and keeping the references, use

=INDIRECT("'" & prevSheet(GoogleClock()) & "'!" & CHAR(64+COLUMN()) & ROW()

This was very helpful to get me started.

posted at 01:21 | path: /web | permanent link to this entry

Tue, 24 Feb 2015

A new blog entry in Markdown format

I took the markdown plugin from here and fixed a few issues, most notably making it an entry parser, losely based on the plugin.

This is awesome, because now I can use github to actually write blog entries in the browser - maybe, just maybe making the hurdle a bit less to write a few posts.

The resulting code can be found in my new github repository.

And yes, I know, version 1.5 will bring this by default - but I am on Debian Wheezy which runs 1.4.

posted at 23:42 | path: /web | permanent link to this entry

Wed, 03 Dec 2014

Installing a StartSSL certificate with haproxy

haproxy since version 1.5 supports native SSL. It uses a combined PEM file for encrypting connections.

StartSSL is a popular provider of free SSL certificates (which I happen to use), but only provides separate ssl.crt and ssl.key files.

To install these with haproxy they need to be combined into a single PEM file. It is helpful to also present the intermediary certificates, which can be downloaded on the StartSSL website as well. Otherwise clients might not recognize the certificate as valid because they cannot verify the certificate chain.

To combine them into one, cat works just fine:

cat ssl.crt ca.pem ssl.key > ssl.pem

Install it in haproxy by adding a bind option to the frontend block, e.g.

bind :443 ssl crt /etc/haproxy/ssl.pem bind :::443 ssl crt /etc/haproxy/ssl.pem

This is what I'm running on

posted at 01:00 | path: /unix | permanent link to this entry

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.