Advice for Scheduling rMarkdown flesdashboard to run and generate HTML output.

[technobrat]

Hi,

I am trying to determine the best way to deliver a flexdashboard HTML without the end user needing to open and run R but at the same time avoiding (if possible, Rserverconnect or Shiny)

Now I've seen that a post with the exact same question, where Shiny/Shinydashboard is suggested using the reactiveFileReader() function, but to my understanding that would keep an R instance open and running which users could allow the user to access the script and alter something by mistake.

https://forum.posit.co/t/best-way-to-automatically-update-data-connection-on-schedule-or-on-changes/43677

I've also see and tried approaches that utilize Task Scheduler and creating a .bat file but this is all new to me so, any advice?

The data is in a remote server running a Windows Server instance, I have a scheduled a SQL job to Query and save a CSV file that is what feeds my Flexdashboard. I wish to have this updated every 10-15 minutes but no clue how to do it and the documentation I found was not helpful.

Any insight is welcome!

[mfherman]

It sounds like you are not using Shiny, and so you want to schedule a script to re-render the flexdashboard HTML every so often. Depending on what operating system you are on, you could use cron or Task Scheduler to run an R script every 15 minutes. If everything is self-contained in the Rmd file, the R script would be something like:

rmarkdown::render(
  input = "my-flexdashboard-script.Rmd",
  output_file = "Dashboard.html",
  output_dir = "path/to/dashboard"
  )

And again depending on your OS, the program you might run could be

Rscript render.R

Where render.R contains the code above. If you can share a little more detail with what you have tried and what issues you are having, we may be able to help more specifically.

[technobrat]

Thank you for your reply!

I really never done this and would appreciate the help.

I am not using shiny because currently I'm more comfortable with flexdashboard and still working on the shiny version for a later date.

I am currently running this scenario on my local workstation, I have remote access via SSMS to the database and peform my sql queries locally and save as CSV for my analysis and dashboard creation.

The below is what I have come up but it is "annoying" part is that I have to go to the project folder and select it all and run it. The dash is generated fine and the R instance is closed so that no one would have a chance of messing up the code since the end user using the dashboard has no idea how to use R.

rmarkdown::render("My_FlexDashboard_V2.Rmd")
quit(save = "no")

What I envisioned for deployment/ implementation for the users:

(simply because I thought this would be the easiest way about it at the time).

Users have already remote server access where they already go in via RDP to the actual server running the SQL query and software.

I have requested RStudio to be installed and I would have SSMS perform my query every 15 or 20 minutes and save the output as a CSV file in the project folder in the server.

I'm kind of stuck on this last "leg" of delivering updating versions of the dashboard hassle free to users.

I don't know if this is possible, due to lack of experience, but can an R app be made just to run the My_FlexDashboard_V2.Rmd and close R? That way I could instruct the users just click on this, after it runs, open the HTML file with the flexdashboard

Or would it be best to have R open in the background running this rmarkdown::render() script only, so if someone messes up it's an easy fix. On a side note for this render() function, I did not see a "time" variable, how would you change it to every 30 min or 5min?

Any feedback would be greatly appreciated.
Thanks!

[technobrat]

@mfherman

I've done more research and here are my findings, unfortunately still not being able to get Task Scheduler to open R properly and run the script.

The below is what I've saved as: Render_Script.R on project folder.
'My_FlexDashboard_V2.Rmd` works fine if I either run the script manually or the rmkd file manually.

rmarkdown::render("My_FlexDashboard_V2.Rmd")
quit(save = "no")

1st approach got stuck with Task Scheduler not sure how to open script.

Here are a few screen shots:

Setting up Task:

Setting up Task trigger:

Setting up Task Action:

If I ran or waited the schedule to hit the time and run it would stop at this:

2 - Possible alternative creating a .bat file and run that:

Also did not work:

Basically trying what I found on here:
https://bigdataenthusiast.wordpress.com/2016/09/10/scheduling-r-script-using-windows-task-scheduler/

@echo off # did try ~\R.exe as well...
C:\Program Files\R\R-4.0.1\bin\x64\Rscript.exe CMD BATCH C:\Users\...\Project_Folder\Render_Script.R

The above seemed like it would open and close the cmd window really quick and nothing happened.

Any input is much appreciated.
Thanks!

[technobrat]

Hi all,

After hours and hours of trial and error and a lot of research, I have figured out what worked.

Unfortunately the "native" windows solution, I could not get Task Scheduler to work.

Fortunately, I was able to figure it out otherwise:

My catharsis came from CRAN itself & the script log.
First I read this article:

TaskScheduleR: taskscheduleR

I opted to utilize the addin, and also noted that the error log was accusing of two things:

1. Pandoc was missing: Pandoc Installation
2. For some reason the file path used in windows wasn't working, but I remembered that maybe the path format should follow UNIX, so I just swapped the forward slash to back-slashes.

rmarkdown::render("C:/Users/etc.../ProjectFolder/My_Flexdashboard.Rmd")
quit(save = "no")

I wonder if I can control the taskscheduler via cmd line to adjust to other intervals than what is presented in the addin interface.

Hope this helps and wonder if others have more experience with this and have better advise or inputs.

Cheers.

[mfherman]

Sounds like you got it up a running! I am my no means an expert here, but just in case you want to try something else out, the way I have my daily tasks set up in Task Scheduler looks like this:

Where the Add arguments option looks like this:

/c bash "C:/path/to/project/my-script.sh"  >> "C:/path/to/project/my-log.log" 2>&1

This runs my-script.sh and appends the output to my-log.log after each run. In my case, my-script.sh executes a few R scripts and does a few other things so it looks something like:

#!/bin/sh
Rscript "R/rscript-1.R" &&
Rscript "R/rscript-2.R" &&
Rscript "R/rscript-3.R"

[technobrat]

Thanks for the reply,

So you schedule your Task Scheduler to open the cmd.exe, as Add arguments is a running a .sh file (bash?) how do you script that in PowerShell? Could that .sh run a SQL query on a SSMS and then sequentially run the r script?

I'm trying to learn your approach as an alternative to have in the belt for a rainy day. But seems interesting how you can run these.

Using your method does it run in the background or does it open a terminal screen and then closes when it is done? Because my approach has that inconvenience of out of nowhere, when R task schedule runs it pops this screen that I would rather have it run "incognito" in the back.

Thanks for sharing your experience this is very helpful!

Cheers.

[mfherman]

That’s right. In your shell script you could include anything that can be executed from the command line. In my case it is usually a series of Rscript commands and then a git commit and push. But you certainly could run SQL. I don’t know how SSMS works but if there is a command line utility you could just have that in your shell script before the R stuff happens.

As for PowerShell, you should just be able to launch that instead of cmd.exe in Task Scheduler.

I don’t have any solution to the terminal screen popping up when it is running...I usually just minimize it and ignore.