Download LabArchives Widget Content for Spreadsheets
How to create an output table with the form entries from LabArchives widgets for further data analysis
This article is part of a series on how to get started with designing custom LabArchives Widgets. You can find the introduction and links to the other articles in the series here.
The generalized, and updated, code to implement an output table is available in my GitHub repository at https://github.com/gibson-amandag/LabArchivesWidgetTemplates/blob/main/_commonComponents/userInteractions/show-save-copyTable/jsPieces.js
Table of Contents
Oftentimes, I’m designing a widget to collect data that I then want to add to an Excel file to use for analysis. While just having the data easily visible in the form is helpful, it’s more helpful to be able to easily export this data from the widget into my Excel file, facilitating creation of a database.
To do this, I create an “output table” with columns arranged to match my Excel file. Then, the values of the input from the form can be used to fill in the table.
Populate Table
The screen capture above demonstrates that there are a few options for how to update this table with the form values. You can set up event listeners that check for changes to specific elements and then run a function upon change to update the table, as demonstrated by Column C.
//When text is input for column c, update the table
$("#ColumnC").on("input", function () {
var $elToWatch = $(this);
var $elToUpdate = $("#ColumnC_calc");
my_widget_script.watchValue($elToWatch, $elToUpdate)
});I add this chunk within the addEventListeners method. The watchValues method takes the value of the input element $elToWatch and then updates the $elToUpdate with that values. This $elToUpdate can also be a class rather than an id as demonstrated here, which makes it easy to update multiple parts of the page with the save value.
You can also automate this further by adding a class to form elements that you want to use to fill your table or other elements on the page. I use the class simpleCalc. Then, when I’m designing the output table HTML, I add a class to the output cells where the class name is the ID of the element to watch with _calc appended to the end. When I update an element with the simpleCalc class, the Javascript code will get the ID of this element, add _calc to the end and use my watchValues() function to update all of the output cells will the value of my input form elements.
The HTML for the Column C calculation might look something like this:
<div class="col"><input type="text" class="fullWidth simpleCalc" id="ColumnC" name="columnc"></div>
....
<td class="ColumnC_calc"> </td>Calculate Values
Or, you can use a Calculate Values button to wait and update multiple values at once. As discussed above, I have also specified that Column B has to be filled out for the table. When the Calculate Values button, or any of the other buttons in the row, is pressed, I first check if the data is valid and update the error message, then run calcValues.
$('#calculate').on("click", function () {
//run to give error, but allow to calc regardless
my_widget_script.data_valid_form();
my_widget_script.calcValues();
});calcValues( )
This function gets the value of the Column A and Column B inputs and updates the corresponding elements within the output table. If there are any blank values or NaN values within the table, it changes these to NA, which is my preference for analysis with R.
data_valid_form( )
This method checks each element with the class needForTable. If there is not a value for any of those elements, it changes the state of value to false. You could uncomment the name and fail_log lines to display an alert to the user of which elements needs to be completed. Currently, if the data is not valid, it just updates the error message (errorMsg) to ask the user to fill out the necessary elements. If the data is valid, it clears the error message. The state of valid is returned, so this can be used for making decisions within other functions.
Show/Hide Table
toggleTable button is used to show or hide the output table (outTable). Again, I make sure that the data is value, I recalculate the values to make sure that they are up-to-date, and then I toggle the table and resize the container to make sure that everything fits appropriately. The first call of my resize method is important if you are using the window width to change the width of the table <div>, as you want to update this before you show the table.
$("#toggleTable").on("click", function () {
my_widget_script.resize();
my_widget_script.data_valid_form();
my_widget_script.calcValues();
$("#tableDiv").toggle();
my_widget_script.parent_class.resize_container();
});Save CSV
$('#toCSV').on("click", function () {
var fileName //TO DO add fileName
var tableID //TO DO add tableID
var $errorMsg //TO DO add error message as jQuery my_widget_script.toCSVFuncs(fileName, tableID, $errorMsg);});
These functions save the contents of outTable to a CSV file. In this case, if the data_valid_form method returns a false value, then I do not run the export function, and I update the error message to indicate this. If the data is valid, then I recalculate the values and export the table to a CSV.
exportTableToCSV( )
This method is used to take the contents of the outTable and make a CSV with these values. It collects the rows of the table. For each row, it then searches for the <td> and <th> elements, which are the columns cols. For each element of cols, it gets the text of those cells and adds this to a row array. These different cells are then joined together into one string, with each cell separated by a comma ,. The row is added to the csv array, and then the individual rows of this csv array are joined into one string, separated by \n, the new line marker. This is passed with the filename to the downloadCSV method.
downloadCSV( )
This method takes the csv generated by exportTableToCSV and creates a link within the document object model that it then clicks to begin the download of the CSV file. The original source for these two functions is here: ttps://www.codexworld.com/export-html-table-data-to-csv-using-javascript/
Copy Data
The copyDataButton copies the outTable contents to the clipboard, which can then be pasted in a spreadsheet program, such as Google Sheets or Excel. With the “Copy Table Head” checkbox, the user can indicate if they also want the table header or just the body when they press Copy Data. Because I am often pasting into a table that already exists, I usually only want the data itself and not the column names. When the button is pressed, I again check if all of the needForTable elements have been filled out, as well as checking the state of the copyHead checkbox. I recalculate the table values. If the data is valid, then I show the table, run the copyTable method, and let the user know that the table has been copied successfully. If the data is not valid, then I update the error message to let them know that nothing was copied. I have also added the option to transpose the table.
$("#copyDataButton").on("click", function () {
var $copyHead //TO DO add table
var $tableToCopy //TO DO add table
var $tableDiv //TO DO add tableDiv
var $errorMsg //TO DO add error message
var $divForCopy //TO DO add div where the table copies to
var $transpose //TO DO add transpose checkbox my_widget_script.copyDataFuncs($copyHead, $tableToCopy, $tableDiv, $errorMsg, $divForCopy, $transpose)});
copyTable( )
This function first creates a temporary <textarea>, $temp. However, as described in the Tips and Tricks article, there cannot be an actual <textarea> tag within the script editor, as the page thinks that it is exiting the script editor field and gets rid of the rest of the script. Therefore, this has to be separated and reconstructed into a string.
This function creates a rows array and for each rowNum it added an array within this array. For each cell within the output table, the value is added to the row array with push. If copyHead is true, it adds the cells within the thead. For each rowNum, the values in this subarray are join'ed with a \t to add a tab between each cell. Then, the rows are join'ed with a \n to put them each on a new line.
$temp is added to the $tableDiv, and then the focus is shifted to this element, it is selected, and then copied. Because the focus is shifted to $temp, it should be added to a part of the page that is close to the Copy Data button to avoid moving the page for the user. $temp is finally removed from the document.
