I am trying to read from a spreadsheet put out by our finance team daily with the aim of importing this data into our MySQL DB.
Complicating matters is that the column headers are dynamic - i.e. change by date. For example here’s a sample header.
"CURRENT CASH BALANCE as of report run date 06/29/2023"
These get translated as keys when I try to set variables which results in
{{ $json['CURRENT CASH BALANCE as of report run date 06/29/2023'] }}
Clearly this won’t work tomorrow unless I change the keys.
Is there any way I can refer by column position? {{$json[5]}}? Failing that, can I use the fact that the first part of the column name doesn’t change - the CURRENT CASH BALANCE part above?
So originally I was thinking maybe a code node would be the option here, Do a loop over the keys and it it matches your pattern do a rename. I then remembered that we have a “Rename Keys” node which I have not used before.
Turns out it has an expression option in it so you can just pop in CURRENT CASH BALANCE as of report run date \d\d\/\d\d\/\d\d\d\d as your regex option and then a new value under it like CurrentBalance and problem solved.
