You can pass values from the worksheet to the widget and vice versa, but only for widgets with HTML content type.
Passing values from the worksheet to the widget
Values are passed to the widget in a pre-defined JavaScript variable array called params. Of course, you first need to define a data source in the widget properties. For example, if your source is the range A1:B2, the cell value from A1 would be accessible in the widget code with the expression params[0], while B1 would be params[1], and so on.
The following example is a JavaScript function that reads this variable and assigns the values to an object in the HTML part of the code, making it visible. This function __exec is executed by default every time source values change, so the widget refreshes the data.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
</head>
<body>
<script type="text/javascript">
// define the function with your source as parameter
function __exec(params) {
// declare the object "vCont" to which you later want to "assign" the source content
var vCont = document.getElementById('valContainer'),
// Initialize the output variable "vContCnt" by first setting a line break
vContCnt = '<br>';
// Fill the output variable "vContCnt" in a loop, concatenating all values from the source array
for (var param in params)
vContCnt = vContCnt.concat(params[param], '<br>');
/* publish the output variable "vContCnt" inside the HTML tag of the object "vCont" declared above. This is done by
manipulating the "innerHTML" property of the object "vCont" */
vCont.innerHTML = vContCnt;
}
</script>
<b>Source data: </b><br />
<!-- The function defined above manipulates the content of the following div-tag at runtime to contain our source values -->
<div id="valContainer" style="position: inherit; width: 200px; height: 200px;"></div>
</body>
</html>Passing values from the widget to the worksheet
To return a value to the worksheet (or more specifically, to the target defined in the widget properties), the pre-defined JavaScript function __set() is used. As its single parameter, __set expects the value to return.
The following example code returns the current value of a text input field to the worksheet once the user clicks the button:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<p>Test!</p>
</head>
<body>
<input type="text" name="val" id="inputval"/>
<input type="button" value="Update target" onclick="__set(document.getElementById('inputval').value);" />
</body>
</html>Of course, the target of the widget must be specified to pass the result back to the spreadsheet.
Updated February 7, 2024