Batch Spreadsheet Worksheets & Automations

1. Create a Batch Spreadsheet Worksheet
2. Create a Test Spreadsheet Worksheet
3. Create Example Batch and Test
4. Create a Batch Automation
   
   1. Automation Setup
   2. Automation When Setup
   3. Automation Then Setup
   4. Using VLOOKUP
5. Final Usage Example

Webinars

The videos below feature expert insights, real-world examples, and best practices from our recent webinars. Each session is designed to help you maximize our platform’s value:

Configuration

Dollar Reference Items

Mapping Items

Batches of Batches

Building a Batch

Example Batch Workflow

Create A Batch Spreadsheet Worksheet

Below is an example Batch Spreadsheet worksheet and is the example that we will use throughout this tutorial. 

Some Important things to note from the above worksheet:

- We are getting the data from the Tests on the Batch. This means that when we create our Batch, we will need to assign Tests to the batch.

 

Create A Test Spreadsheet Worksheet

The data from out Batch Spreadsheet worksheet will be copied over to this Test Spreadsheet worksheet that we need to create. Below is an example of the Test worksheet we will use in this example:

We are going to also create a named cell in this spreadsheet worksheet and call it `cbd`. This is so when we create our Batch Automation to copy from the Batch Worksheet we know where to put the value.

 

Create Example Batch and Test

Once we have our Batch and Test worksheets setup, we need to...

1. Create a Batch and assign the Worksheet to it:

2. Create a Test and assign the Test Worksheet to it (by creating an assay and assigning the assay to a Sample):

3. Assign our Test to our Batch so the Test appears in our Batch Worksheet:

This is what our Batch worksheet should look like after we have assigned our Test to the Batch.

 

Create a Batch Automation

Now we are ready to set up our Batch Automation to copy data from the Batch Worksheet over to our individual Test Worksheet.

Automation Setup

• Trigger: select "Data Modified"
• Data Type: select "Batch".
• Ensure you check the "Active" checkbox and create your new Automation!

 

Automation When Setup

• Field Type: Select "Data Type"

• Field Name: Select "Worksheet"

• Before Conditional: Select "Anything"

• After Conditional: Select "Equal To" and select the Batch Worksheet that we created "No Code Training Batch WS 2"

Note: This "When" criteria will only trigger our automation for a Batch that has our Batch worksheet assigned.

 

Automation Then Setup

This is how we will set up the "Then" part of our Batch Automation:

• Action: Set value on all Test Worksheets within the Batch
  • This action will allow us to update the test worksheets of all the tests assigned to our batch and will provide the `test` variable to our context in the Worksheet Field Name field.
• Worksheet Field Name: cbd
  • This is the named cell we created in our Test Worksheet.
• To: Copy Value
  • This option is because we would like to copy values from our Batch worksheet.
• Copy: From Worksheet
  • This selection means we are copying from the Batch Worksheet as opposed to copying from the Batch entity.
• Worksheet Field Name: =VLOOKUP({{test.id}}, B2:D7, 3)
  • More information on this vlookup function in the next section.

 

Using VLOOKUP

In the previous section, we used a special Hyperformula function called "VLOOKUP". This function is used to get data from a spreadsheet by a specific index. Here is the basic anatomy of the VLOOKUP function:

=VLOOKUP(<Key>, <Range>, <Index>)

• Key: This is the unique key that we will use to find our data in the Batch worksheet. In our case, our Key will be the Test ID.

• Range: This is the entire range in the spreadsheet that MUST include both the Key and the target Data. Therefore, in our case, we will set the range to B2:D7 to include both the Test ID column and the Concentration column.

• Index: This is the number of columns to the Right of our Key column starting at the Key column. In our case, the Index will be 3 because our data column is 2 columns to the RIGHT of our Key column.

Note: If your Key variable is a string, you will need to include quotes "" around the Key. For example, if our Key was <test_id>_cbd then our Key in our VLOOKUP would be "{{test.id}}_cbd". However, in this tutorial, our Key is only <test_id> in the Batch worksheet, so our Key in the vlookup only needs to be {{test.id}} without the quotes.

Hence, our vlookup for this tutorial is:

=VLOOKUP({{test.id}}, B2:D7, 3)

Final Usage Example

Now that our Automation has been created, all we need to do is enter a value in the Concentration column for our the Test row in our Batch Worksheet and the value should be copied over to our Test worksheet automagically!