Shiny App reads .xlsx but won't write files or run external R scripts located in same folder

Hi all,

I'd like to start by saying that I am only a novice programmer, if something I say is not clear or lacking important information, please let me know and I'll happily update my post or add that information.

I've created a Shiny app for my work. I'll describe it below, but I think that the nature of my problem unfortunately makes it difficult to create a reproducible example, I'm hoping that my description of the issue will be enough to help solve it. Right now, everything I mention is located on a Dropbox folder.

The app is suppose to do the following:

  1. Read a .xlsx file that is located in the same folder as the app
  2. Render one sheet from the .xlsx file into a table in the app and allow the user to modify the table
  3. Write the user-updated data from the table in the shiny app to an "intermediate" storage location (also .xlsx and located in same folder as the app)
  4. Run an external R script (also currently located in the same folder as the app) that processes the data in the "intermediate" .xlsx and sends it back to the original .xlsx file.

Locally, this works fine. However, the deployed version of the app does not work properly. The deployed app performs tasks 1 and 2 fine. It reads the .xlsx and allows the user to modify the data in the app. However, when I press the button that is supposed to write the updated data to the intermediate file and subsequently run the R script that adds that file to the original .xlsx, nothing happens. I know that the app recognizes the "intermediate" file and the external r script because if I introduce typos into the file names of either, the app breaks.

Does anyone have any insight into why this might be happening, and any way I might be able to fix it? I've been stuck on this for some time and this is the only thing preventing my work from using the app. Any insight would be greatly appreciated. Thank you in advance.

If you own the server you deployed to, you can try to look at this issue from a file permissions standpoint.
If you are using some hosting service like shinyapps.io , you shouldnt expect to have live write access at runtime to their app folders...

Something you should consider is to utilise temp directories. base r provides a tempdir() function you can use to get a directory path you can freely read and write any files to.

If you want to ultimately update i.e. write to, your storage, that is another level of considerations, can you guarantee that no more than one user will use the app and try to edit the same file at the same time how would you handle if they did ? It may be revealed that an excel file is not an advisable tool for the task. Perhaps creating a database with commits will be more robust and flexible

putting that aside you should consider further efforts in shiny debugging... it is acceptable to add a logger so that you can have your app, and external r script both logging their activity/ or lack thereof, so you can determine what happened when. daroczig/logger: A lightweight, modern and flexible, log4j and futile.logger inspired logging utility for R (github.com)
add info logs to tell yourself where you are in your code.
add stop conditions to test if things are wrong.

final thought, what is the utility of the external R script, perhaps it would be maintainable / easier to debug if it was a part / module of your main app proper ?

Hi thanks for the extra information.
Perhaps a relatively simple thing you could try is make a very minimal shiny app, that tries to do nothing but write a dataset (perhaps iris ?) to your dropbox location ?
I often find its easier to diagnose issues and fix them when the code I'm reviewing has a very narrow focus.

I've done some troubleshooting and determined that shinyapp.io cannot write to Dropbox at all, regardless of any file type of specific code used. I wrote some simple test code that uses "rdrop2" to write a simple CSV. When I launch the app by using the "Run App" button in R Studio, it does what it's supposed to do and writes the CSV in the folder. However, here again the shinyapp.io does not work and the file does not appear. That makes me think it's something about the accessibility or permissions to Dropbox, as you indicated, rather than something odd happening with the file type or code. Even using the "rdrop2" package which apparently many people use in shiny apps to interact with Dropbox (I was using the drop_upload function, specifically) it did not work. Thanks again for your help, hopefully someone on here can offer some insight because I feel like I've tried everything.

Did it fail silently or fail with errors in the app logs?

In the app it fails silently as far as I can tell. The app stays connected and I don't get any error messages. I don't think it's an issue with the code since launching the app from R studio performs as expected.

It is possible the file is getting written to a different location? Do you by any chance know how I might check that?

I recommend you use rsconnect::showLogs()
To see the logs generated over at shinyapps.io

I can't seem to launch the app this way by including the app directory in showLogs(). There are a lot of message in the console mostly describing installing packages. The only thing that looks odd is this message at the end:

2020-12-07T19:48:21.288088+00:00 shinyapps[3354452]: Waiting for authentication in browser...
2020-12-07T19:48:21.288350+00:00 shinyapps[3354452]: Press Esc/Ctrl + C to abort
2020-12-07T19:48:21.289857+00:00 shinyapps[3354452]: Please point your browser to the following url:
2020-12-07T19:48:21.290135+00:00 shinyapps[3354452]: https://www.dropbox.com/oauth2/authorize?client_id=mmhfsybffdom42w&redirect_uri=http%3A%2F%2Flocalhost%3A1410%2F&response_type=code&state=I6ResgbRSq

When I try to open that link it takes me to the rdrop2 authentication page. After I confirm authentication, I get an error and the app does not launch.

I think the website for rdrop2 talks about setting up authentification tokens for shiny. I think its something like saving them to disk as an RDS and then passing them. The rds can be bundled by rsconnect with the rest of your app when deploying.

I did go through that documentation again. As far as I can tell rdrop2 is authenticated properly. I think this should work even using base R. At any rate, still not able to get it to work.

Hello, thanks for your reply. I'm not sure exactly what is meant by if I own the server I deployed to, but these files are all located on a Dropbox that I do have access to. If the the app can read the files but not write to them, do you think that relates to some type of permission issue? I had come across information about the tempdir(), but even if I am able to write to that directory, I'm not sure that solve my issue because eventually the data does need to leave the app. Maybe I am misunderstanding how that works? You are probably correct that using a .xlsx file may not be the best for this application as I've described it. However, due to the nature of other things we use this data for, it is easier for us to access and maintain in that form. There are only a few of us using the app and although multiple users using the app may be an issue, since there are only a few potential users I think we might be able to avoid that for the most part. At you suggestions, I will probably look into finding a way to prevent multiple users at once. I will try adding the debugger to see what shows up when I run the code. Finally, the utility of the external R script is just to make things a little more manageable. The app at this point is relatively large, and I was just trying to keep things in smaller modules. At some point if I can't get this to work including the code in the extenral R script may be a possibility to get the app to function. However, since the app won't write to the "intermediate" file, I don't think that including the external R script code in the app would work anyway. Thank you for your suggestions.