Skip to content

Instantly share code, notes, and snippets.

@rmeekers
rmeekers / node_red.json
Last active May 16, 2022
Node Red flow to monitor Eeve / Toadi to Home Assistant via MQTT (including auto discovery) - https://community.home-assistant.io/t/eeve-toadi-willow/405097
View node_red.json
[
{
"id": "76106b40e1ce3c2f",
"type": "tab",
"label": "Toadi to MQTT",
"disabled": false,
"info": "",
"env": []
},
{
View Regex to split street into street, number and box
^(\w[\s\w\-\'.]+?)\s?((?:\d+(?:\s?-\s?\d+)?)\s?[a-zA-Z]?)(?:\s?-?\s?)?((?:\/|bus|Bus)\s?.+)?$
@rmeekers
rmeekers / vlookup_in_multiple_columns.md
Last active Apr 24, 2020
Google Sheet formula to do a vlookup in multiple columns
View vlookup_in_multiple_columns.md

Assumptions:

  • value to look for is in cell C2
  • array to search in: 'email domains'!A:Y
  • value to retrieve is in column no 26 (Z)
=ArrayFormula(if(countif('email domains'!A:Y;C2)=0;"";index('email domains'!A:Z;sum(if('email domains'!A:Y=C2;row('email domains'!A:Y))); sum(if('email domains'!A:Y=C2;26)))))
@rmeekers
rmeekers / difference between straight and curly quotes.md
Created Mar 13, 2020
What's the difference between straight and curly quotes?
View difference between straight and curly quotes.md

Straight quotes are the generic vertical quotation marks which are inserted via the quotes button on a keyboard. There are two variants:

  1. the straight single quote (') and 2 .the straight double quote (").

Curly quotes are the quotation marks used in good typography. There are two sets of curly quote characters:

  1. Single quotes: the opening single quote (‘) and the closing single quote (’)
  2. Double quotes: the opening double quote (“) and the closing double quote (”).

How to use these quotes in a text editor?

View convertXMLtoHtml.md

This describes how you can convert an XML file to a simple HTML table. The table can then be copy/pasted into a Google Sheet for example for further manipulation.

Install Saxon

brew install saxon

Convert XML file to HTML

saxon -s:input.xml -xsl:stylesheet.xsl -o:result.html

Sample XSL

View Import CSV in Google Sheets.gs
/**
* Set of functions to import CSV files into a Google Sheet
* Imports CSV as text, freezes the first row, sets it bold and removes empty rows and columns.
*/
var settings = {
CSVFolderID: '',
singleFileID: '',
CSVSeparator: ';'
}
@rmeekers
rmeekers / importNumberOfInternetUsersPerCountry.gs
Created Jun 6, 2018
Formula to import the number of internet users for a given country
View importNumberOfInternetUsersPerCountry.gs
/*
* Datasource: public Google Spreadsheet with internet user data:
* https://docs.google.com/spreadsheets/d/1uxPf0einHJxZqkTvXnFto0O8Q4Lxw9RBPKoBCV2idTE/edit
*
* In the example below, we expect one or more country codes (ISO 3166-1 alpha-2), separated by a pipe (|), in cell A2
*/
=
SUM(
QUERY(
IMPORTRANGE("1uxPf0einHJxZqkTvXnFto0O8Q4Lxw9RBPKoBCV2idTE";"Data!B:C");
@rmeekers
rmeekers / code.gs
Created Dec 11, 2017
Bulk Convert XLSX Files in Google Drive To Google Sheet
View code.gs
/*
* Setttings
*/
// Google Drive Folder ID which contains the Excel files you want to convert
var driveFolderId = '';
// Folder name in which we should place the original files after conversion
var sourceFilesFolderName = 'Source Files';