Example Procedures - Using Secret Links

For this example, we will make a table of data that is displayed in a filtered manner when users visit particular secret links.

At several points in the tutorial, you will need to press Upload Changes before you can make any changes. You should press it whenever you see the button appear.

Step 1 - Example User Data

Enter the following data in Excel:

Step 2 - Make it a table
Select A1:B3 and use Insert > Table in the Excel toolbar. Ensure "My table has headers" is checked.
Call the table Users using the table name edit box that appears in the Excel toolbar.

Step 3 - Example data to be filtered

Create a new sheet called Friends and enter the following information:

Step 4 - Make that a table too
Select A1:B6 and use Insert > Table to turn the data into an Excel table. Again, ensure that the header checkbox is checked.
Call the table Friends using the table name edit box that appears in the Excel toolbar.

Step 5 - Make a page that shows all the friends
Using the Sheetcast sidebar, add a new Report page. For the source of the page, select the Friends table.

Press Save and Exit.

Step 6 - Make a link that goes to the user's secret key

On the list of pages, use the clipboard icon to copy the ID of the page.

In the cell to the right of the first row in the Users table (D2), add the formula:

=CASTPAGELINK(CONCAT("Log in as ", [@Username]), "<Page ID>", CASTPARAM.COOKIE("login-key", [@Key]))

Replace the <Page ID> part with the ID from your clipboard.

See the later Explanation section for details on how this works.

Step 7 - Filter the friend's page using the logged in user.

Open up the report page via the Sheetcast sidebar. Click on the More tab. In the Filter section, you should see Classic Excel Formula selected. In the formula box underneath, put:

@A:A=XLOOKUP(CASTPARAM("login-key"),Users[Key],Users[Username])

Step 8 - Add a page that allows to test the login links

Add a new Report Page using the Users table as its data source.

Step 9 - Add public sharing

Go to the share settings for the page and ensure it's shared publicly. Anyone will be able to access it, but it will be filtered to show only ones own data.

Step 10 - Try it out

Click on the eyeball in the user page you just made to go to the page with the test login links

Explanation

CASTPAGELINK builds a link that the user can click on their browser to visit one of your Sheetcast pages. You need to tell it which page to visit and if there are any extra pieces of information that page will need.

CONCAT("Log in as ", [@Username]) builds the link that the user will see. You could alternatively use a simple string, such as "Login".

CASTPARAM.COOKIE("login-key", [@Key])) is the important part of this link. When the user clicks the link, the system will cause the secret key (coming from the Key field in the table) to be stored in a parameter called login-key. We will then be able to access the parameter using CASTPARAM("login-key") from other formulas. Under the hood, the key is stored as a cookie in the browser. If you want the cookie to persist longer than when the user closes their browser, you can change that by specifying a number of seconds as the third parameter to CASTPARAM.COOKIE.

Once we have that parameter stored in the cookie, then whenever we visit a page, we'll have the user's secret key available for use in formulas. The way we use it in this example is to filter the friends table with the following formula:

@A:A=XLOOKUP(CASTPARAM("login-key"),Users[Key],Users[Username])

This means to only show friends where the value in column A (the username) is the same as the username that matches the current secret key.

CASTPARAM("login-key") retrieves the secret key that is available in the cookie provided by the browser.

XLOOKUP(CASTPARAM("login-key"),Users[Key],Users[Username]) retrieves the username that matches the secret key.

Then finally we just use = to compare the username in the first column of the friends table with the username we looked up that matches the secret key. If they match, the record is shown.

Generating secret keys

You can make a secret key using a formula like "secret-key-"&TEXT(RANDBETWEEN(1, 9999999), "0000000") in combination with a custom form button that performs a DOADDRECORD. A full explanation is out of scope for this article. This approach to generating secret keys might generate duplicates. A better solution is forthcoming and this documentation will be updated when it is available.