Rutger Meekers rmeekers

## node_red.json
[
  {
    "id": "76106b40e1ce3c2f",
    "type": "tab",
    "label": "Toadi to MQTT",
    "disabled": false,
    "info": "",
    "env": []
  },
  {

## 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?.+)?$

## vlookup_in_multiple_columns.md

      
              1 file
            
          
              0 forks
            
          
              0 comments
            
          
              0 stars
            
          
                rmeekers
                / vlookup_in_multiple_columns.md
            
            
              Last active
              April 24, 2020 07:13
            
              
                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)))))


## difference between straight and curly quotes.md

      
              1 file
            
          
              0 forks
            
          
              0 comments
            
          
              0 stars
            
          
                rmeekers
                / difference between straight and curly quotes.md
            
            
              Created
              March 13, 2020 11:13
            
              
                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:

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:

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

How to use these quotes in a text editor?

  
## convertXMLtoHtml.md

      
              1 file
            
          
              0 forks
            
          
              0 comments
            
          
              0 stars
            
          
                rmeekers
                / convertXMLtoHtml.md
            
            
              Last active
              September 24, 2019 12:39
            
          
      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

  
## 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: ';'
}

## gist:69cb2ec85440b7709c75baebfd13b014
<tag>[\s\S]*?</tag>

## Extract filename from URL in Google Sheets
=TRIM(RIGHT(SUBSTITUTE(A2;"/";REPT(" ";100));99))

## 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");

## 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';
	[
	{
	"id": "76106b40e1ce3c2f",
	"type": "tab",
	"label": "Toadi to MQTT",
	"disabled": false,
	"info": "",
	"env": []
	},
	{
	/**
	* 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: ';'
	}
	/*
	* 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");
	/*
	* 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';