Configure automatic run of XL based climate obs data prep

[franTarkenton]

an R based shiny app has been created to review / fix / the climate observations data. Need to:

• identify the code used to prep the Xl Spreadsheet
• Modify the paths
• Containerize
• Integrate the running of this code as a GHA.

Significant amount of work to accomplish this task. Another task will also be defined that will port the existing R based Shiny app to run on openshift.

[franTarkenton]

Had a thought that it may be possible to run the original XL code more easily.

Idea:

• open the spreadsheet using python and the win32com module on windows vm
• identify and modify the cells that describe the source paths for the incomming data
• modify those paths to where the data is actually located.
• Attempt to run the macro from python that performs the injestion.

stack exchange post on running macros from python: https://stackoverflow.com/questions/19616205/running-an-excel-macro-via-python

[franTarkenton]

Created the following branch with the xl code in it. Its looking entirely possible. Potentially can re-use this pattern for all the input spreadsheets that go into the coffee model.

Branch with the code is: https://github.com/bcgov/nr-rfc-climate-obs/tree/feat/15-xl-ingestion

[franTarkenton]

Latest commit now updates the paths to the source data, however it does not update the hyperlink for the cell... Need to figure that out.

Also now updated the export models now get trimmed to only coffee and ensemble V

[franTarkenton]

file path hyperlinks now getting updated.

Tmrw work on surrounding data imports:

• create / pull weekend data, and re-run the import to see if it works.
• also run todays data and import it.
• start with fresh ss.

Next look into running as gha

[franTarkenton]

working on getting the preparation of the wildfire weather stations (F_WX) data working and into directories / formats / file names that work with the xl injestion process. Currently running into issues with getting the pulled data and the R script to work together

[franTarkenton]

Shift is stragegy:

1. will pull the climate obs data down from their remote servers and do any transformations required.
2. climate obs data prep spreadsheet will be pulled from object storage.
3. update process will be run on the SS, which changes the default paths for where the input data is expected to come from
4. update process will run the macro
5. Run the coffee model (NOT PART OF THIS TICKET)

In order to enable this all to take place a sync process needs to be setup that watches the on prem versions of the spreadsheet and pulls that version of the SS. Currently setting up a sync process that will copy updates of the SS from on prem to object storage so that that data is available for this process. The process will be deployed using the GIS jenkins server

[franTarkenton]

On prem -> Object store sync

• Defined a jenkins job to perform the sync
• Discovered the service account doesn't have access to the model path where the climate_obs excel ss is located.
• Tried running the jenkins job under my idir (domain controller doesn't allow this)
• Removed credentials from the job and reverted back to original
• Created the following issue to track the service account creation: issue #42

[franTarkenton]

the work associated with this ticket has been merged with main (https://github.com/bcgov/nr-rfc-climate-obs/pull/59), however the github action that was intended to run the climateobs xl macro is not enabled. the GHA is https://github.com/bcgov/nr-rfc-climate-obs/blob/main/.github/workflows/run_climate_obs.yaml however none of the triggers will fire.

Moving this ticket to cancelled as we have pivoted our approach to try to re-create logic in the xl macro as a python script