Placeholders¶
Syntax¶
Google sheets may contain placeholders that are recognized by the Google Sheets Connector when sheets are added. These placeholders indicate where live values should be substituted. Placeholders take the following form. In this example, placeholders are named “tag1”; the placeholder name is exposed as a tag connection point in tasks.
| Placeholder | Live Value substitution relative to the placeholder cell |
|---|---|
#tag1# |
In-place |
#:tag1# |
Left |
#tag1:# |
Right |
#|tag1# |
Above |
#tag1|# |
Below |
#:append# |
Append rows below and on the left for all placeholder cells on the left |
#append:# |
Append rows below and on the right for all placeholder cells on the right |
In addition to the tag value, placeholders or target cells may also be substituted with a tag value’s timestamp, status, status text, or name, by appending a suffix to the placeholder:
| Short Suffix | Long Suffix | Substitution |
|---|---|---|
#tag1.v# |
#tag1.value# |
Value (the default when a suffix is ommitted) |
#tag1.t# |
#tag1.timestamp# |
Timestamp (UTC) |
#tag1.s# |
#tag1.status# |
Status Code (a zero value here means the value is “Good”) |
#tag1.st# |
#tag1.statustext# |
Status Text (i.e., “Good”) |
#tag1.n# |
#tag1.name# |
Name of the tag |
Tip
Use placeholders in staging sheets that do not substitute live values “in-place”. For example, use placeholders that substitute live values on the right of the placeholder cell instead. This makes it easier to add placeholders later since previous placeholders remain even after some live values are written.
Excel RTD links¶
When importing Excel sheets containing RTD links, then these links appear as formulas in the Google sheet but are not further processed. When you add such a Google sheet to the Google Sheets Connector then RTD links are interpreted as in-place placeholders and are expected to be in the format generated by the Excel Connector. These RTD links take the following form:
=RTD("Rensen.Dataristix.RTD",, "<Connector Name>", "<Topic Name>", "<Tag Name>")
When such an RTD formula is found in your Google Sheet then tags are generated from the tag name and optionally from the connector name or topic name (according to the configuration).
You can now create data processing tasks that send data to the Google Sheet by substituting live values in-place into the RTD formula cells.
Warning
Cells containing links will be overwritten with live values when data transfer tasks are started. Ensure to keep a duplicate of your Google Sheet containing the original links.
Excel OPC Office Link links¶
When importing Excel sheets containing OPC Office Link style links, then these links appear as formulas in the Google sheet but are not further processed. When you add such a Google sheet to the Google Sheets Connector then OPC Office Link links are interpreted as in-place placeholders.
Linked data groups¶
OPC Office Link links for data groups and the tags contained within the data group take the following form.
=Rensen.OPCRemoteLinkServer.1|'C:\Users\Public\Documents\Rensen\OPC Office Link\Groups\Data1.rol'!'!''Data1'''
Data group links contain the path to the data group file and the data group name (here, Data1.rol). Before you can
add a Google sheet containing such links you need to ensure that the data group file is available in the indicated location.
If you have installed Dataristix onto the same computer as OPC Office Link, then this will usually be the case. If you
have installed Dataristix onto a different computer, then copy relevant data group files within the containing folder
(i.e., C:\Users\Public\Documents\Rensen\OPC Office Link\Groups) into the same folder on your Dataristix computer prior
to adding the Google sheet to the Google Sheets Connector. Create the folder if it does not exist.
When a data group link formula is found in your Google Sheet then tag names are read from the corresponding data group file and tags are generated from the tag name and optionally from the data group name if you have opted to include additional RTD topics (according to the configuration).
You can now create data processing tasks that send data to the Google Sheet by substituting live values in-place into the data group link cells.
Warning
Cells containing data group links will be overwritten with live values when data transfer tasks are started. Ensure to keep a duplicate of your Google Sheet containing the original links.
Linked tags¶
OPC Office Link links for individual tags take the following form.
=Rensen.OPCRemoteLinkServer.1|'C:\Program Files (x86)\Rensen OPC Office Link\opclink.rol'!'!''Opc.Da.ServerName''!Tag1'
Tag links contain the originating OPC DA server reference (Program ID) and the tag name (here, Tag1).
When a tag link formula is found in your Google Sheet then tags are generated from the tag name and optionally from the
OPC DA server program ID if you have opted to include include additional RTD topics
(according to the configuration).
You can now create data processing tasks that send data to the Google Sheet by substituting live values in-place into the tag link cells.
Warning
Cells containing tag links will be overwritten with live values when data transfer tasks are started. Ensure to keep a duplicate of your Google Sheet containing the original links.
Add placeholders to your sheet¶
Enter placeholders directly into cells¶
Open your Google sheet, then type the placeholder text into cells following the format described above. After you have finished editing placeholders, add the spreadsheet to the Google Sheets Connector. See an example here.
Copy a connector topic¶
Right-click on a connector topic of another connector in the navigation panel (for example, the OPC UA or MQTT connector), then select “Copy Google Sheet placeholders”. This will place placeholders for all tags within the connector topic onto the clipboard; you can then paste the placeholders into your Google Sheet. After you have finished preparing your spreadsheet, add the spreadsheet to the Google Sheets Connector. See an example here.
“Send to” New Google Sheet¶
Right-click on a connector topic of another connector in the navigation panel (for example, the OPC UA or MQTT connector), then navigate to menu “Send to” / “Goggle Sheets Connector” / “New Google Sheet”. The dialog will prompt for required details.
Enter your spreadsheet ID (1) or simply copy the URL of your spreadsheet (for example “https://docs.google.com/spreadheets/d/2UV7qd0Z_dgwqTc8QQ7fgyuWtyghIPa5TbZGee-8A-qD…”) that is to contain the new sheet, enter the new sheet name (2) and optionally check “Append values” (3) to append values in new rows. Make sure the the service account configured for the Google Sheets Connector has access to the spreadsheet.
Finally, press the “Create” button to create the new Google sheet. At the same time, a new task is created to transfer data to the new sheet and you can begin sending data to the sheet by starting the task.