Save/Load rpivottable configuration

Question

I use rpivottable on several (rmarkdown) web pages.

I have seen an example here of saving/restoring table configuration to/from cookie. Since I am not good in javascript, I would like to ask if it is possible to programmatically add two buttons in the rmd page, on top of the table control, allowing the users to save/load their preferred table configurations (either to cookie, or to a local file, if possible). Could you provide sample code to achieve that?

Thanks.

Answer 1

This one took a while. I used local storage. I've got a lot of styling here, but it's unnecessary. I used the output of flexdashboard, since that tends to cause me the most problems with JS.

<style>
body {    /*push content away from far right and left edges*/
  margin-right: 2%;
  margin-left: 2%;
}
.rpivotTable {
  overflow:auto;
  resize: both;
  box-shadow: 0 22px 70px 4px rgba(0,0,0,0.56);
  -moz-box-shadow: 0 22px 70px 4px rgba(0,0,0,0.56);
  -webkit-box-shadow: 0 22px 70px 4px rgba(0,0,0,0.56);
  -moz-border-radius: 5px;
  -webkit-border-radius: 5px;
  border-radius: 5px;
  border: 1px solid white;
  padding: 5px;
  margin: 5px 20px 50px 5px;
}
.btn {
  vertical-align: middle;
  -moz-box-shadow: 0px 10px 14px -7px #000000;
  -webkit-box-shadow: 0px 10px 14px -7px #000000;
  box-shadow: 0px 10px 14px -7px #000000;
  -moz-border-radius: 4px;
  -webkit-border-radius: 4px;
  border-radius: 4px;
  border: .5px solid black;
  display: inline-block;
  font-size: 1.3em; 
  padding: .3em 0px;
  width: 18em;
  text-decoration: none; /*no underline!!*/
  cursor: pointer;
}
.btn:active { /*simulate movement*/
  position: relative;
  top: 1px;
}
</style>

I've used the content that I've found in other questions.

## R Markdown

<div style="margin-right:5%;">

`r stringi::stri_rand_lipsum(10)`

</div>

```{r cars}

library(rpivotTable)

data(mtcars)
names(mtcars)[10] <- "George.Dontas"

```

Here is the **first** Div.

## Including Plots

Do you want to save or restore the previously saved pivot tables' configuration?

<a id='saveBtn' class='btn' style="background-color:#003b70;color:white;">Save Current Configuration</a>
<a id='restoBtn' class='btn' style="background-color:#b21e29;color:white;">Restore Previous Configuration</a>

```{r pressure, echo=FALSE, fig.show="hold"}
rpivotTable(mtcars,rows="George.Dontas", cols=c("cyl","carb"),width="100%", height="400px")
```

```{r morePressure, echo=FALSE, fig.show="hold"}
rpivotTable(mtcars,rows="George.Dontas", cols=c("cyl","carb"),width="100%", height="400px")
```

This should be a different aspect of the report.

```{r evenMorePressure, echo=FALSE, fig.show="hold"}
rpivotTable(mtcars,rows="George.Dontas", cols=c("cyl","carb"),width="100%", height="400px")
```

Here is the JS/JQuery...it's a bit ugly and a rather unseemly hodgepodge of the two (JS/JQuery).

```{r listenOrElse,results="as-is",engine="js"}

// save current state of the tables to my browser
setTimeout(function(){       //add the events first
  document.querySelector('a#saveBtn').addEventListener('click', savoring);
  document.querySelector('a#restoBtn').addEventListener('click', giveItBack);
  function savoring() {                             // function to save
    el = document.querySelectorAll('.rpivotTable');
    for(i=0; i < el.length; i++){
      elId = el[i].getAttribute("id");
      stringy = $('#' + elId).data("pivotUIOptions"); // collect rows/columns filters
      delete stringy['aggregators'];                 // remove the arbitrary
      delete stringy['renderers'];
      stringy2 = JSON.stringify(stringy);            // make it one key:value
      window.localStorage.setItem('table' + i, stringy2); // store it!
    }
  };
  function giveItBack() {                           // function to regurgitate
    el = document.querySelectorAll('.rpivotTable');
    console.log("working on the giver");
    ods = [...el[0].ownerDocument.scripts];         // make it an array
    for(j=0; j < el.length; j++){
      elId = el[j].getAttribute("id");
      where = ods.filter(function(ods){             // filter scripts for table data
        return ods.dataset['for'] === elId;
      })[0].innerHTML; 
      where2 = JSON.parse(where).x.data;            // WOOO HOO! I figured it out!!
      where3 = HTMLWidgets.dataframeToD3(where2);   // finally sheesh!!
      gimme = window.localStorage.getItem('table' + j); // get storage
      $('#' + elId).pivotUI(where3, JSON.parse(gimme), true, "en"); // put it back!
    }
  }
},100);

```

---

---

Update

Thanks for pointing out some opportunities for improvement, @George Dontas. This update changes how the configuration is saved. I'm sure there are still ways to improve it, though.

This update adds the file or webpage name as part of the key-value pair used to store the information. Now, both the name of the webpage/script and table number need to match for the tables to update. Additionally, this will alert the user when a configuration cannot be restored. This alert would occur if there is nothing saved and if there is no file name and table matching configuration saved.

Updates to Saving the Configuration

There is one new line and one modified line of code in savoring().

New:

path = window.location.pathname.split("/").pop().split(".").slice()[0]; //f name

Modified:

window.localStorage.setItem(path + '_table' + i, stringy2); // store it

The entire function with changes:

  function savoring() {                     // function to save
    el = document.querySelectorAll('.rpivotTable');
    path = window.location.pathname.split("/").pop().split(".").slice()[0];
    for(i=0; i < el.length; i++){
      elId = el[i].getAttribute("id");
      stringy = $('#' + elId).data("pivotUIOptions"); // collect filters
      delete stringy['aggregators'];        // remove the arbitrary
      delete stringy['renderers'];
      stringy2 = JSON.stringify(stringy);   // make it one key:value
      window.localStorage.setItem(path + '_table' + i, stringy2);  // store it
    }
  };

Updates to Restoring the Configuration

There are few new lines in this function. The name has to be collected, as in the savoring() changes. Additionally, this function now has an alert for the user.

I started out with the basic system alert, but it wasn't up to snuff for my tastes, so I also developed a custom alert box. I've included both here.

Basic Alert and Updated Configuration Retrieval

The only thing that changes from my original answer to making a basic alert are the following lines of code within the giveItBack() function:

path = window.location.pathname.split("/").pop().split(".").slice()[0]; //f name

and

  if(window.localStorage.getItem(path + '_table' + j) === null) {
    jj = j + 1;
    alert("WARNING: There is no saved pivot table configuration for " + path + "'s table " + jj + ".");
    continue; // don't update, go to next table (if more than 1)
  }

Here is the complete giveItBack() function (note that notice(msg) and msg are here, but commented out):

function giveItBack() {               // function to regurgitate
    el = document.querySelectorAll('.rpivotTable');
    console.log("working on the giver");
    ods = [...el[0].ownerDocument.scripts];   // make it an array
    path = window.location.pathname.split("/").pop().split(".").slice()[0]; //name
    for(j=0; j < el.length; j++){
      elId = el[j].getAttribute("id");
      where = ods.filter(function(ods){     // filter scripts data
        return ods.dataset['for'] === elId;
      })[0].innerHTML; 
      where2 = JSON.parse(where).x.data;    // WOOO HOO! I figured it out!!
      where3 = HTMLWidgets.dataframeToD3(where2); // finally formatted
      // is there a saved configuration that matches this file and table?
      if(window.localStorage.getItem(path + '_table' + j) === null) {
        jj = j + 1;
                  //this is for the standard alert box
        alert("WARNING: There is no saved pivot table configuration for " + path + "'s table " + jj + ".");
        //msg = "<b>WARNING</b><br><br>There is no saved pivot table configuration for<br>" + path + "."
        //notice(msg); //this is for the custom alert box
        continue; // go to next loop
      }
      gimme = window.localStorage.getItem(path + '_table' + j); // get storage
      $('#' + elId).pivotUI(where3, JSON.parse(gimme), true, "en"); // put it back!
    }
  };

Custom Alert and Updated Configuration Retrieval

If you choose to use a more custom approach to the alert message, there is a lot more (luckily, it should be copy and paste). You will use the giveItBack function from the updates for the basic alert, but comment out or delete alert(... and uncomment msg and notice().

For the CSS in my original answer, update the styles for .btn to .btn, #noted and .btn:active to btn:active, #noted:active.

This is the remaining CSS for the custom alert. You can add this CSS to the other style tags or keep them separated.

<style>
#notice-wrapper {
  width: 100%;
  position: fixed;
  top: 0;
  left: 0;
  z-index: 1000000;
  background: transparent;
  display: none;
  transition: opacity 1s ease-in;
}
#notice-box {
  -moz-box-shadow: 0px 10px 14px -7px #000000;
  -webkit-box-shadow: 0px 10px 14px -7px #000000;
  box-shadow: 0px 10px 14px -7px #000000;
  border-radius: 4px;
  border: .5px solid black;
  width = 300px;
  background: #003b70;
  color: white;
  min-height: 200px;
  position: absolute;
  top: 50%;
  left: 50%;
  margin: -100px 0 0 -150px;
}
#notHead {
  text-align: center;
  font-size: 1.3em;
  padding: 4px;
  margin: 2.5em;
  font-family: Verdana, sans-serif;
}
#noted {
  background: #b21e29;
  margin: .5em;
  width: 120px;
  font-family: Verdana, sans-serif;
}
</style>

The JS for the custom alert box is next. I placed this function within the setTimeout(function(){ with savoring() and giveItBack().

  function notice(msg) {
    function cr() {
      if(document.querySelector('#notice-wrapper') === null) {
          wrapper = document.createElement('div');
          wrapper.id = 'notice-wrapper';
          html = "<div id='notice-box'><h2 id='notHead'></h2><div id='noticeBtns'>";
          html += "<button id='noted'>OK</button></div></div>";
          wrapper.innerHTML = html;
          document.body.appendChild(wrapper);
      }
      insta = document.querySelector('#notice-wrapper');
      placer(insta);
      return(insta);
    }
    function placer(insta) {
      wrapper = insta;
      winHeight = window.innerHeight || document.documentElement.clientHeight || document.body.clientheight;
      wrapper.style.height = winHeight + "px";
    }
    function showy(el) {
      el.style.display = "block";
      el.style.opacity = 1;
    }
    function goAway(el) {
      el.style.opacity = 0;
      setTimeout(function(){
        el.style.display = "none";
      }, 1000);
    }
    function takeAction(msg) {
      insta = cr();
      insta.querySelector('#notHead').innerHTML = msg;
      showy(insta);
      insta.querySelector('#noted').addEventListener('click', function() {
        goAway(insta);
      }, false);
    }
    takeAction(msg);
  }

Of course, with this custom option, you have the opportunity to style it as you see fit. Style control isn't an option with the system alert messaging system.