Instructions

Contents:
1. Connect to your spreadsheet
2. Create the search form
   2.1 Search fields
   2.2 Dropdowns
   2.3 Checkboxes
   2.4 Search history
3. Adjust your templates
   3.1 Short presentation
   3.2 Long presentation
4. Create search shortcuts
5. Add your own logo
6. Embed X-ref in your website
7. Links, Images, Addresses, Phone
   7.1 Link button
   7.2 Images
   7.3 Addresses
   7.4 Telephone
8. PDF reports
   8.1 PDF-report based on several rows in your spreadsheet - several rows per PDF-page
   8.2 PDF-report based on several rows in your spreadsheet - one row per PDF-page
   8.3 PDF-report based on a single row in your spreadsheet
   8.4 PDF-report based on a single row in your spreadsheet - upload to Google Drive
   8.5 Images in PDF reports
9. Filters
10. Bookmarks and Subsets
11. Skip short presentation and go directly to long presentation
12. Export search result
13. Search multiple Google Sheets

1. Connect to your spreadsheet to top

Under Settings, enter the url to your Google Sheet

If your Google Sheet is not public then Google will propt users to login to their Google account before they can use X-ref. The Sheet can be set public in the top right corner of your sheet:

2. Create the search form to top

To configure your search form you need to update your menu.

2.1 Search fields to top

A,B,G and H in above image are references to the column headers in your spreadsheet.

The type attribute can have the following values:

number (like in above H field): use if the sheet column consists of numbers only
date: use if the sheet column consists of dates only
wholeField: use if you want the whole field to be searched in stead of just the beginning of the field
exact: search for the whole word, not the beginning of the word
autoSuggest like this:

autoSuggestMulti select several words:

autoSuggestMultiWholeField same as above but whole filed is searched, not just the beginning

A search field can be requiered which means that the search field has to have a value before a search can be executed.
<field sheetColumn="A" label="Name" required="true"></search-field>

If you want to search several columns from a search field, do like this: SheetColumn="A+B+C"

Here is an example template

2.2 Dropdowns to top

Dropdowns are configured like this:
<dropdown sheetColumn="E" label="My dropdown">AAA,BBB,CCC</dropdown>

2.3 Checkboxes to top

Checkboxes are configured like this:

COLUMN_B is a reference to the column in your spreadsheet, sport is the search word, ? implies that not only the word sport will be queried but also sports, sporting etc.

Several columns can be connected to a checkbox
<facet-field search="COLUMN_A art? AND COLUMN_B sports?">Art and sport</facet-field>

A tip if you do not want to type this manually is to click the default button:

2.4 Search history to top

This search form saves a user's search history and it is possible to combine prevoius searches using AND, OR, NOT.

The above search form only works on wide screens so it is important to have a fall back presentation for mobile presentations.
Here is an example template

This film describes combining search results:

3. Adjust your templates to top

Two templates are used to present the search result.

3.1 Short presentation to top

The Short presentation template controls how the fields are presented here:

You control this presentation by changing this section of the template:

A,B,C and H are references to the columnheaders in your spreadsheet.

If you want to display several columns under the same header, use a plus sign, like this: ...,A+B+C,..."

3.2 Long presentation to top

The Long presentation template controls how the fields are presented here:

You control this presentation by changing this section of the template:

A,B,C,D,E,F,G,H and I are references to the columnheaders in your spreadsheet.

4. Create search shortcuts to top

You can have as many search shortcuts (CA and NY in above image) as you wish. To add/remove search shotcuts you update the menu.

The the top menu item (States in above example) is of type Head and its purpose is just to open the menu. To create search shortcuts start by executing a search from the search form. Then use this part of the url:

Paste it in here:

5. Add your own logo to top

6. Embed X-ref in your website to top

Embedding is not recommended.

7. Links, Images, Addresses, Phone to top

7.1 Links to top

It is possible to create link-buttons which when clicked will redirect the user to the underlying url.

To create a link-button in the short template add a features attribute.

In the short template (above) it is important to count the commas (columns) so that the button ends up in the correct column. The number of commas (columns) should be the same in codes, widths, headers and features.

To create a link-button in the short template add a feature attribute.

7.2 Images to top

It is possible to display small images (thumbnails) which when clicked will redirect the user to the underlying image. The sheet column should contain an url to the image. (If the image is a Google Drive image, use the format: https://drive.google.com/uc?id=XXXXXXXX)

To display an image in the short presentation add a features attribute in the short template.

In the short template (above) it is important to count the commas (columns) so that the image ends up in the correct column. The number of commas (columns) should be the same in codes, widths, headers and features.

100 in above template is the width of the image

To present an image in the long template add a feature attribute.

7.3 Addresses to top

It is possible to create links which when clicked will redirect the user to Google maps. The sheet column should contain a valid adress. To create an address link in the short presentation add a features attribute in the short template.

In the short template (above) it is important to count the commas (columns) so that the address link ends up in the correct column. The number of commas (columns) should be the same in codes, widths, headers and features.

To present an address link in the long template add a feature attribute.

7.4 Telephone to top

It is possible to create links which when clicked will open a telephone app on your phone/desktop if there is one installed. The sheet column should contain a telephone number. To create an telephone link in the short presentation add a features attribute in the short template.

In the short template (above) it is important to count the commas (columns) so that the telephone link ends up in the correct column. The number of commas (columns) should be the same in codes, widths, headers and features.

To present a telephone link in the long template add a feature attribute.

8. PDF-reports to top

The PDF-reports are based on Google Docs templates. Make sure that your Google Docs templates are publicaly available.

8.1 PDF-report based on several rows in your spreadsheet - several rows per PDF-page to top

To set this up start by creating a Google Docs document template:

The Google Docs document template has to contain a table. B,H,I and J are references to the columns in the spreadsheet.

Here is an example template.

Enter the Google Docs Url under Settings in Short presentation:

Make sure that the Google Doc is publicaly available.

Add a report button in your short presentation template:

8.2 PDF-report based on several rows in your spreadsheet - one row per PDF-page to top

Follow the steps above but design your Google Docs template differently.

Here is an example template which includes a cover page and footer.

8.3 PDF-report based on a single row in your spreadsheet to top

To set this up start by creating a Google Docs document:

A and B are references to the columns in the spreadsheet.

Here is an example template.

Enter the Google Docs Url under Settings in Long presentation:

Make sure that the Google Doc is publicaly available.

Add a report button in your long presentation template:

8.4 PDF-report based on a single row in your spreadsheet - upload to Google Drive to top

It is possible to create up to 10 single row PDF-reports at the time. These reports are uploaded to your Google Drive. The PDF-filname is fetched from a column in your sheet.

Here is an example template.

Enter the Google Docs Url under Settings in Long presentation:

Make sure that the Google Doc is publicaly available.

In your short presentation template, add a report button like this:
<report-button type="MultiplePdfsToDrive">Upload Pdfs to Google Drive</report-button>

8.5 Images in PDF reports to top

To dynamically insert images in your report add the following:
X-REF-IMAGE:{M},100,,THUMB
M is a reference to the column in your spreadsheet
100 is the width of the image
THUMB will make the image clickable

(If the image is a Google Drive image, use the format: https://drive.google.com/uc?id=XXXXXXXX)

9. Filters to top

Adjust below code according to your preferences then add it at the top of your Short presentation template:

<search-form>
<refine-dropdown sheetColumn="H" label="Car brand"></refine-dropdown>
<refine-dropdown sheetColumn="I" label="Car color"></refine-dropdown>
<br />
<refine-selected-words></refine-selected-words>
</search-form>

This film describes the filter function:

10 Bookmarks and Subsets to top

By clicking the Create Subset button, a subset search result containing the selected rows is created. By clicking the Move to Bookmarks button, selected rows are moved to the Bookmarks page where they are stored between searches.

On the Bookmarks page it is possible to delete or execute a search for these bookmarks.

Setup

1) Create a new column in your spreadsheet which should contain uniqe numbers.
To create such a column, fill in 1 in row 1 and 2 in row 2, then drag and drop:

2) Select the unique column under Settings in your administration.

There are two modes:

I) Bookmarks and Subsets: Checkboxes are cleared every new search. Selected items are moved to the Bookmarks page by clicking the Move to Bookmarks button.
II) Only Bookmarks: Selected checkboxes remain checked between searches. Items are moved directly to the Bookmarks page when checked.

3) Add buttons in the template. The following text should be added to your Short template to add the related buttons:

<subquery-button>Create Subset</subquery-button>
<move-to-bookmarks-button>Move to Bookmarks</move-to-bookmarks-button>

Also add a CheckBox column.

4) Finaly add the Bookmarks page in your Menu.

11 Skip short presentation and go directly to long presentation to top

By adding a longpres="true" attribute to your search button code the short presentation is skipped.

12. Export search result to top

Add the following code:

<export-button delimiter="tab" codes="A,B,C" fileName="myFile.txt" fileType="text/plain;charset=utf-8">Export</export-button>

(adapt according to your preferences) to your Short presentation template.

Like this:

13. Search multiple Google Sheets to top

It is possible to search multiple Google sheets with one search query. Add the urls for these Google sheets.

By default, all the added sheets are searched. However, if you wish to search for instance only sheet number 1 and 3, it is possible to specify this in your search form like this:

It is possible to display colors indicating from which sheet the row belongs.

To display these colors add SheetColor in your Short template.