Tag Archives: bash

No more protected sheets

I needed to get some data from protected sheets.

But I got this on 100+ files:

F that:

Made a script to remove sheet protection and ran this in a loop over all files.
Bye bye protection.

 ./script.sh /mnt/fileserver/sensordata001.xlsx
Parsing /mnt/fileserver/sensordata001.xlsx
Archive:  this.zip
  inflating: tmp/[Content_Types].xml
  inflating: tmp/_rels/.rels
  inflating: tmp/xl/_rels/workbook.xml.rels
  inflating: tmp/xl/workbook.xml
  inflating: tmp/xl/worksheets/sheet4.xml
  inflating: tmp/xl/worksheets/sheet2.xml
  inflating: tmp/xl/worksheets/sheet3.xml
  inflating: tmp/xl/worksheets/sheet1.xml
  inflating: tmp/xl/styles.xml
  inflating: tmp/xl/theme/theme1.xml
  inflating: tmp/xl/sharedStrings.xml
  inflating: tmp/docProps/core.xml
  inflating: tmp/docProps/app.xml
  adding: [Content_Types].xml (deflated 78%)
  adding: docProps/ (stored 0%)
  adding: docProps/core.xml (deflated 49%)
  adding: docProps/app.xml (deflated 54%)
  adding: _rels/ (stored 0%)
  adding: _rels/.rels (deflated 60%)
  adding: xl/ (stored 0%)
  adding: xl/worksheets/ (stored 0%)
  adding: xl/worksheets/sheet2.xml (deflated 92%)
  adding: xl/worksheets/sheet1.xml (deflated 46%)
  adding: xl/worksheets/sheet4.xml (deflated 92%)
  adding: xl/worksheets/sheet3.xml (deflated 92%)
  adding: xl/_rels/ (stored 0%)
  adding: xl/_rels/workbook.xml.rels (deflated 77%)
  adding: xl/workbook.xml (deflated 56%)
  adding: xl/theme/ (stored 0%)
  adding: xl/theme/theme1.xml (deflated 78%)
  adding: xl/styles.xml (deflated 57%)
  adding: xl/sharedStrings.xml (deflated 23%)

Bash script:
It leaves the original intact, but makes a unprotected copy named the same with unprot_ in front of it.

if [ -n "$1" ]; then
  echo "Parsing $1"
  echo "usage : scriptname /path/to/sheet.xlsx"
  exit 0
name=$(basename $1)
dir=$(dirname $1)
rm -f this.zip
rm -rf tmp
mkdir tmp
cp "$1" this.zip
unzip this.zip -d tmp
find tmp/xl/worksheets/ -iname *xml -exec sed -i -e "s/<sheetProtection.*\/>//g" {} \;
cd tmp
rm -f "$dir/unprot_${name}"
zip -r "$dir/unprot_${name}" *
cd ..

Server scripts notification for Home Assistant

I’m running loads of housekeeping scripts on my servers.

I thought it would be cool to see states in HA.


  • Log into your HA instance, and press your profile icon in the bottom left.
    Scroll to Long-lived access tokens, and create a new token.
    (Save the token string in a text file, you need it later)
  • Goto Settings > Devices & services > Helpers
    Create helper: Text and give it a name (bashnotification)
  • Next create a script in a path on your server, or place in an existing script directly.
    (Change SAVEDTOKENSTRING,HA-IP and bashnotification)
curl -s -X POST -H "Authorization: Bearer SAVEDTOKENSTRING" -H "Content-Type: application/json" -d "{\"state\": \"$1\"}" http://HA-IP:8123/api/states/input_text.bashnotification >/dev/null

I use it like this in a script

bashnotify "Starting this script"

bash commands bash commands 
bash commands 
bash commands bash commands bash commands 
bash commands bash commands 

bashnotify "Bash command finished"

Running an adhoc command

tar czvf /tmp/test.tgz /var/www/html ; bashnotify "tarball made of www"

Kodi camera stream push playlist

Below is a solution when you want to stream IP camera’s in Kodi/Libreelec .

You can push these commands using Nodered, Bash script or whatever.

First make some camera scripts in your profile directory.


# Kodi on Linux/Raspberry
# Place a file cam1.m3u in .kodi/userdata/profiles/(kodiprofile)/playlists/video/

#and another one in cam2.m3u (another example mjpeg example)

#For windows it is in

Enable http access in Kodi and run the playlist using curl

curl -i -X POST -H "Content-Type: application/json" -d '{"jsonrpc":"2.0","method":"Player.Open","params":{"options":{"shuffled":false,"repeat":"off"},"item":{"file":"special://profile/playlists/video/cam2.m3u"}},"id":"1"}' http://KODISERVERIP:8080/jsonrpc

A bash loop script

while true; do
curl -i -X POST -H "Content-Type: application/json" -d '{"jsonrpc":"2.0","method":"Player.Open","params":{"options":{"shuffled":false,"repeat":"off"},"item":{"file":"special://profile/playlists/video/cam1.m3u"}},"id":"1"}' http://KODISERVERIP:8080/jsonrpc
sleep 10
curl -i -X POST -H "Content-Type: application/json" -d '{"jsonrpc":"2.0","method":"Player.Open","params":{"options":{"shuffled":false,"repeat":"off"},"item":{"file":"special://profile/playlists/video/cam2.m3u"}},"id":"1"}' http://KODISERVERIP:8080/jsonrpc
sleep 5

Find the most perfect loop part in a movie clip using ImageMagick

Step 1 : Convert movie to png’s

ffmpeg -i mymovie.mp4 %04d.png

Step 2 : Run script in same directory

#set -x
numba=$(ls *png | wc -l)
numbastart=$(( $numba - 10))
numbapadding=$( printf "%04d\n" $numba)
numbapaddingstart=$( printf "%04d\n" $numbastart)
echo "$f "
mkdir -p images/$f
mkdir -p metric/$f
for x in $(seq -w 1 $numbapaddingstart) ; do
	a=$(( $x + 10))
	for y in $(seq -w $a $numbapadding) ; do

	compare -fuzz 20% -verbose -metric $f  $x.png $y.png images/$f/$x-$y.png  2> metric/$f/$x-$y.txt
	echo -n "."

echo ""

Step 3 : There are metric stats in a subdirectory, let’s find the most matching parts (top 10)

: > /tmp/top10
more metric/MAE/* | grep all   | awk '{ print $2 }' | cut -f1 -d. | sort -n |head | while read ; do
grep -H all metric/MAE/* | cut -f1,2 -d.  | grep " $REPLY" >> /tmp/top10
cat /tmp/top10 | cut -f3 -d/ | cut -f1 -d. | while read part ; do
	echo mkdir -p "$part"
	startpart=$(echo $part | cut -f1 -d-)
	endpart=$(echo $part | cut -f2 -d-)
	for file in $(seq -w $startpart $endpart) ; do
		echo cp 0${file}.png $part/
	echo cd "$part" 
	echo ffmpeg -y -framerate 30 -pattern_type glob -i \'*.png\'  -c:v libx264 -pix_fmt yuv420p out.mp4
	echo cd $orgpwd

Run above script as ./script.sh > mybash.sh

This generates a bash file, check the contents and run using

“bash mybash.sh”

Last step : There are 10 movies in subdirs which should contain the best looping parts.
check these with: (use CTRL-Q in vlc to stop looping and go to the next file

ls */out.mp4 | while read movie ; do vlc -L $movie ; done

Example loop, made with above

Obfuscating color logo in bash

Something from my old archive.

Colleage’s used to like the method.

source with color escape code applied
32=green 31=red and 97=white

echo  -e "\e[0;32m '.::///+:/-.        --///+//-:'' " 
echo  -e "\e[0;32m  '+oooooooooooo:   '+oooooooooooo: " 
echo  -e "\e[0;32m   /oooo++//ooooo:  ooooo+//+ooooo. " 
echo  -e "\e[0;32m   '+ooooooo:-:oo-  +o+::/ooooooo: " 
echo  -e "\e[0;32m    ':oooooooo+''    '.oooooooo+- " 
echo  -e "\e[0;32m      ':++ooo/.        :+ooo+/.' " 
echo  -e "\e[0;31m        ...'  '.----.' ''.. " 
echo  -e "\e[0;31m      .::::-'':::::::::.'-:::-'            \e[0;97m    www.henriaanstoot.nl" 
echo  -e "\e[0;31m     -:::-'   .:::::::-'  '-:::-           \e[0;97m    Test logo"
echo  -e "\e[0;31m    '::.  '.--.'  '' '.---.''.::' " 
echo  -e "\e[0;31m        .::::::::'  -::::::::' ' " 
echo  -e "\e[0;31m  .::' .:::::::::- '::::::::::''::. " 
echo  -e "\e[0;31m -:::' ::::::::::.  ::::::::::.':::- " 
echo  -e "\e[0;31m ::::  -::::::::.   '-::::::::  :::: " 
echo  -e "\e[0;31m -::-   .-:::-.''....''.-::-.   -::- " 
echo  -e "\e[0;31m  .. ''       .::::::::.     '..'.. " 
echo  -e "\e[0;31m    -:::-'   -::::::::::'  .:::::' " 
echo  -e "\e[0;31m    :::::::' -::::::::::' :::::::. " 
echo  -e "\e[0;31m    .:::::::  -::::::::. :::::::: " 
echo  -e "\e[0;31m     '-:::::'   ..--.'   ::::::. " 
echo  -e "\e[0;31m       '...'  '...--..'  '...' " 
echo  -e "\e[0;31m             .:::::::::: " 
echo  -e "\e[0;31m              '.-::::-'  " 

Obfuscate the logo to a file (1 time only)

cat source | base64 - -w0 1> ~/bin/logo.ob

Writes file (safe to copy paste/transfer) with:

Create an alias with the logo

alias logo=$(cat ~/bin/logo.ob | base64 -d )

executing logo will produce the logo

Triple screen panorama viewer

Got a question, could I make the video viewer also for images.

Well, that is a great idea, i’ve got some panoramic photos myself.

A little modification, some added code, but here is a working example.

Some vacation pictures widescreen …

imageview.py filename
Use esc to stop, and enter for next image.
(Has better full screen experience than my movie player. (no padding) have to revisit that one )

Nice to have?

  • Back button?
  • Comments, renaming thumb
from pathlib import Path
from sys import platform as PLATFORM
import os
import re
import PySimpleGUI as sg
from PIL import Image, ImageEnhance, ImageTk, ImageOps, ImageFilter
from xml.etree import ElementTree as ET
import sys
from sys import platform as PLATFORM

abspath = os.path.abspath(__file__)
dname = os.path.dirname(abspath)

    print(sys.argv[0] +  " filename")

def nextFile(currentfile,dir):
        dirpath = os.path.dirname(dir)
        fileList = []
        for f in os.listdir(dirpath):
            #fpath = os.path.join(dirpath, f)
            fpath = f
            if os.path.isfile(fpath) and f.endswith(('.jpg','.JPG')):
        for i in range(len(fileList)):
                if (fileList[i]) == currentfile:
        return newfile
# yeah i know .. no thumb but full image, change it yourself!
def loadthumb(thumbfile):
    # IF exists
    path_to_file = thumbfile
    path = Path(path_to_file)

    if path.is_file():
        im = Image.open(thumbfile)
        im=ImageOps.contain(im, (5760,5760)) 
        thumbimage = ImageTk.PhotoImage(image=im)


#------- Layout image only --------#
layout = [
        [[sg.Image('', size=(5760, 1080), key='image',background_color='black',pad=(0, 0))],

#------- Set window --------#
window = sg.Window('Triple image player', layout, no_titlebar=True, margins=(0,0),location=(0,0), size=(5760,1080), keep_on_top=True, finalize=True,resizable=False)

window.bind("<Escape>", "-ESCAPE-")
window.bind("<Return>", "-ENTER-")

window['image'].expand(True, True)               

nextfile = image
#------------ The Event Loop ------------#
while True:
    event, values = window.read(timeout=1000)       # run with a timeout so that current location can be updated
    if event == sg.WIN_CLOSED:

    if event == '-ENTER-':
        nextfile = nextFile(nextfile,'./')

    if event == '-ESCAPE-':

Converting images for right resolution from a temp directory filled with large panorama photos

ls temp  | while read; do
	convert -resize 5760x -gravity center  -crop 5760x1080 -auto-orient  "temp/$REPLY" "$REPLY" 

Background switcher Ubuntu

I was playing around with Phantomjs a headless browser.
Using this as a scraper for a ajax enabled site.

After scraping a wallpaper site, I wanted to take the big pictures and display these as background.

First sort and resize to a better size.

Below does the following:

  • Image width larger than 1800 AND
  • Image height larger than 900
  • Resize to 1920×1080 ( enlarge or reduce size )
  • Not screen filling (portrait mode) ? Than add black bars on the side.
  • Place the image in wallpaper directory

Convert script, if you resize huge images beforehand, you safe cpu resources later.
You also can place other colors or even another background instead of black.

mkdir -p wallpaper
ls * | while read ; do
info=$(identify "$REPLY" | awk '{ print $3 }' 2>/dev/null)
height=$( echo $info | cut -f2 -dx)
width=$( echo $info | cut -f1 -dx)
if [ $width -gt 1800 ] && [ $height -gt 900 ] ; then
	convert -resize 1920x1080  -gravity center  -background black -extent 1920x1080 "$REPLY" "wallpaper/$REPLY"

Set a random wallpaper as background using cron.


cd /home/henri/Pictures/
getranpic=$(ls  wallpaper/ |sort -R |tail -1)
#gsettings set org.gnome.desktop.background picture-options 'wallpaper'
#Set different modes ( enum 'none' 'wallpaper' 'centered' 'scaled' 'stretched' 'zoom' 'spanned' )
gsettings set org.gnome.desktop.background picture-uri-dark  "$(realpath wallpaper/$getranpic)"
logger "$(realpath $getranpic)" 

Cron example

* * * * * DBUS_SESSION_BUS_ADDRESS=unix:path=/run/user/1000/bus /home/henri/bin/setrandom

Planxty Irwin Concertina Notation

While working on a harmony for Irmgard and me in Musescore, i tought it would be nice to have it also in another notation.

Above a Musescore screenshot.

When using below button assignment, we can easily rewrite above into another notation.

Write musescore as MusicML/mxl Music xml.

Install xml2abc from https://wim.vree.org/svgParse/xml2abc.html

python xml2abc.py INPUTFILE.mxl output.abc

My abc file

T:Planxty Irwin
V:1 treble nm="Henri" snm="H"
|: d | B2 d | c2 A | F2 A | G3/2 d/ B | A G F | G3/2 A/ B | D2 E | F2 d | B2 d | c2 A | F2 A |
 G3/2 d/ B | A G F | G3/2 A/ B | e d c | B2 d | B3/2 c/ B | B G B | c3/2 d/ c | c A F | G d e |
 c d e | d3/2 c/ A | d c A | B c d | c B A | F G A | G3/2 d/ B | A G F | G3/2 A/ B | e d c | B2 :|

Using below bash script you can convert this to PDF WITH concertina notations.
WARNING: I didn't include all keys (yet).
NOTE: Easy to adjust to other notations.
if [ $# -lt 2 ]; then
    print "script orgname convertname"
    exit 1
: > parced
cat $1 | awk '/^\|/ {exit} {print}' > header
cat $1 | grep "|" | tr -d '[0-9]:/'> parse
cat parse | while read ; do 
echo $REPLY
echo -n "w: "

for word in $(echo $REPLY) ; do

	if [ "$word" == "|" ] ; then echo -n " | " 
	elif [ "$word" == "D" ] ; then echo -n " 2 ";
	elif [ "$word" == "G" ] ; then echo -n " 3 ";
	elif [ "$word" == "B" ] ; then echo -n " 4 ";
	elif [ "$word" == "d" ] ; then echo -n " 5 ";
	elif [ "$word" == "g" ] ; then echo -n " 6 ";
	elif [ "$word" == "b" ] ; then echo -n " 7 ";
	else echo -n " * "
echo ""

echo -n "w: "

for word in $(echo $REPLY) ; do

	if [ "$word" == "|" ] ; then echo -n " | " 
	elif [ "$word" == "F" ] ; then echo -n " 2 ";
	elif [ "$word" == "A" ] ; then echo -n " 3 ";
	elif [ "$word" == "c" ] ; then echo -n " 4 ";
	elif [ "$word" == "e" ] ; then echo -n " 5 ";
	elif [ "$word" == "f" ] ; then echo -n " 6 ";
	elif [ "$word" == "a" ] ; then echo -n " 7 ";
	elif [ "$word" == "E" ] ; then echo -n " 4' ";  # <============ example 2nd row
	else echo -n " * "
echo ""

) >> parced
cat header parced > $abc
abcm2ps -x -O - "$abc" | ps2pdf  -sPAPERSIZE=a4 - "$(echo  $abc | cut -f2 -d/ | sed 's/abc/pdf/g')"

Example output (Harmony part)

Simple FreeOTP access for PHP

I’ve played with FreeOTP for my own websites in the past, but here is a cleaned up version.

apt-get install qrencode oathtool

Using a webserver with php you can use below simple sniplet to restrict access.

$output = shell_exec('oathtool --totp=sha256 --base32 ################PLACE-SECRET-OUTPUT-HERE-############');
$stripped = preg_replace('/\D/', '', $output);
$strippedotp = preg_replace('/\D/', '', $otp);
<form action="" method="post">
OTP: <input type="text" name="otp"><br>
<input type="submit">
if(strcmp("$strippedotp", "$stripped") == 0)
{ echo "Access"; }
{ echo "No Access"; }

bash script to generate secret and qrcode

secret=$(echo 1234567812345678 | base32)
echo "Secret : (place in PHP file)"
echo $secret
qrencode -t ANSI256 -o - $(echo otpauth://totp/Mine:myname?secret=${secret}\&issuer=Mine\&algorithm=SHA256\&digits=6\&period=30) -o qrcode.png -t png -s 5

Above generates a QRcode you can import in FreeOTP

Scraping authenticated websites

A friend needed to scrape data from an authenticated website.
This needs to be scripted and processed without human intervention.

Following steps are needed to get the correct curl commands (one time only)

Login page
Press F12 or right-click inspect
Click network and reload using ctrl-r
Select the start page and right click
copy as cURL (bash)

next steps

save curl command in a file

remove –compresssion and -H ‘Cookie: JSESSIONID=?????????????????????????????’

add just after curl

-k (no certificate check) and
–cookie-jar tmpcookiefile

excecute this. It will give you a file with a session id and a true field.
(This will change at every login)
but is needed for subsequential requests

Next: use this sessioncookie to get the next authenticated request

So to scrape with login, you need two lines in your script.
One to get the session cookie. (YOUR username/pass will be in here!!)
And the second to get the needed page using the cookie

#authenticate and save sessioncookie
curl -k --cookie-jar part1.cookie 'https://xxx.xxxxx.xxx/site/dologin'   -H 'Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7'   -H 'Accept-Language: en-GB,en;q=0.9,nl-NL;q=0.8,nl;q=0.7'   -H 'Cache-Control: max-age=0'   -H 'Connection: keep-alive'   -H 'Content-Type: multipart/form-data; boundary=----WebKitFormBoundaryb1chvkAVZSF3hPSu'    -H 'Origin: https://xxx.xxxxx.xxx'   -H 'Referer: https://xxx.xxxxx.xxx/site/loginform'   -H 'Sec-Fetch-Dest: document'   -H 'Sec-Fetch-Mode: navigate'   -H 'Sec-Fetch-Site: same-origin'   -H 'Upgrade-Insecure-Requests: 1'   -H 'User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/ Safari/537.36'   -H 'sec-ch-ua: "Chromium";v="110", "Not A(Brand";v="24", "Google Chrome";v="110"'   -H 'sec-ch-ua-mobile: ?0'   -H 'sec-ch-ua-platform: "Windows"'   --data-raw $'------WebKitFormBoundaryb1chvkAVZSF3hPSu\r\nContent-Disposition: form-data; name="form[username]"\r\n\r\nusername\r\n------WebKitFormBoundaryb1chvkAVZSF3hPSu\r\nContent-Disposition: form-data; name="form[password]"\r\n\r\npassword\r\n------WebKitFormBoundaryb1chvkAVZSF3hPSu\r\nContent-Disposition: form-data; name="form[refname]"\r\n\r\n\r\n------WebKitFormBoundaryb1chvkAVZSF3hPSu\r\nContent-Disposition: form-data; name="form[refid]"\r\n\r\n\r\n------WebKitFormBoundaryb1chvkAVZSF3hPSu\r\nContent-Disposition: form-data; name="form[refmod]"\r\n\r\n\r\n------WebKitFormBoundaryb1chvkAVZSF3hPSu\r\nContent-Disposition: form-data; name="form[csrf_hash]"\r\n\r\ncsrf_ab09f7887d9dacfe1489b68b64fe6a01\r\n------WebKitFormBoundaryb1chvkAVZSF3hPSu--\r\n'
#get data from second page
curl -k -l --cookie part1.cookie  https://xxx.xxxxx.xxx/subscriber/overview