SheetRocks Recipes

This guide demonstrates practical patterns and workflows that make SheetRocks uniquely powerful for enterprise use cases.

Lambda Functions & COL Syntax

SheetRocks supports lambda-style operations using the COL keyword, enabling powerful data filtering and transformation without complex formulas.

Basic COL Reference

The COL keyword references entire columns:

COL[A]         # References all values in column A (by letter)
COL[1]         # References all values in column 1 (by number, 1-indexed)
COL[D]         # References all values in column D

Note: COL only supports column letters (A, B, C, etc.) or numeric indices (1, 2, 3, etc.). Column names/headers are not supported.

FILTER with Lambda

Filter rows based on conditions:

=FILTER(A:F, COL[D] = "Active")

This filters the range A:F to only show rows where column D equals "Active".

SUMIF with COL Syntax

Conditional aggregation using COL references:

=SUMIF(E:E, COL[D] = "Paid")

Sums all values in column E where column D equals "Paid".

MAP Function

Apply transformations to ranges:

=MAP(A1:A10, COL[1] * 1.1)    # Increase all values by 10%
=MAP(A1:A10, COL[1] * 2)      # Double all values
=MAP(A1:A10, CELL^2)          # Square each value

Tags and Smart Dropdowns

Tags provide an intuitive way to select and reference Salesforce records within SheetRocks. The TAGS function takes a table with headers and converts it to a JSON array. Shallow JSON objects are automatically rendered as interactive tag pills.

Basic Tags Example

Create tags from a simple table:

=TAGS(A1:B4)

Where cells A1:B4 contain:

label      | color
Option 1   | blue
Option 2   | green
Option 3   | red

This creates JSON objects [{"label":"Option 1","color":"blue"}, ...] displayed as colored tag pills.

Linked Entity Dropdown

A powerful pattern for referencing Salesforce records from a linked sheet:

=TAGS(PICK(Contacts!A:F, "Id", "Name"))

This creates a dropdown showing contact names, but when selected, SheetRocks automatically stores the Salesforce Id. This is perfect for lookup relationships and maintaining data integrity.

How it works:

1. Contacts!A:F references a linked sheet containing Salesforce Contact data (with headers in row 1)
2. PICK(..., "Id", "Name") extracts just the Id and Name columns (preserving headers)
3. TAGS(...) converts the table to JSON objects and renders them as interactive tag pills
4. When a user selects a tag with an "Id" field, SheetRocks stores the Id value
5. The tag renders as a colored pill showing the label

Conditional Formatting

Apply visual formatting based on cell values or conditions.

Format Entire Row Based on Column Value

Make rows green when column D shows "done":

Condition: COL[D] = "done"
Format: Background color green

This rule checks column D for each row and applies green background to the entire row when the value is "done".

Highlight Overdue Items

If your due date is in column F:

Condition: COL[F] < TODAY()
Format: Background color red, text color white

Color-Code by Status

Multiple rules for different status values (assuming status is in column D):

Rule 1: COL[D] = "Complete"   → Green background
Rule 2: COL[D] = "In Progress" → Yellow background
Rule 3: COL[D] = "Blocked"     → Red background

Template Buttons for Record Pages

Launch spreadsheet workflows directly from Salesforce record pages using template buttons.

Creating a Template Button

1. Build your SheetRocks workbook with the desired layout and formulas
2. Save it as a template (note the template ID)
3. On a Salesforce record page, create a button with a specific naming pattern
4. Button name format: _templateId_{TEMPLATE_ID}

Example:

Button Name: _templateId_a1b2c3d4e5

When clicked, SheetRocks launches with the template pre-loaded and automatically:

• Fetches the current record's ID from the page
• Passes it to the template for context
• Enables spreadsheet-in-the-loop workflows tied to specific records

Complete Example: Lead Assignment Workflow

This example demonstrates how to build a lead assignment tool that combines multiple SheetRocks features.

Setup

Sheet 1: Contacts (Linked Sheet)

• Create a linked sheet connected to the Contact object
• Include fields: Id, Name, Email, Title
• This sheet serves as your pool of potential lead owners

Sheet 2: Leads (Linked Sheet)

• Create a linked sheet connected to the Lead object
• Include fields: Id, Name, Company, Status, OwnerId (or Owner lookup field)
• This is your working sheet for lead assignments

Building the Workflow

Step 1: Add Owner Dropdown

In the Leads sheet, add a column for Owner selection:

Column F (Owner): =TAGS(PICK(Contacts!A:D, "Id", "Name"))

This creates a dropdown showing all contact names, but stores their Salesforce Id.

Step 2: Add Status Dropdown

First, create a simple status reference table somewhere (e.g., cells H1:H5):

label
New
Working
Qualified
Converted

Then use:

Column G (Status): =TAGS(H1:H5)

Step 3: Apply Conditional Formatting

Add visual indicators (assuming Status is in column G):

Rule 1: COL[G] = "Converted" → Green background
Rule 2: COL[G] = "Qualified" → Light green background
Rule 3: COL[G] = "New"       → Yellow background

Step 4: Add Filtering

Create a filtered view in another sheet (assuming Status is in column G):

Sheet 3, Cell A1:
=FILTER(Leads!A:G, COL[G] = "New")

This shows only new leads that need assignment.

Step 5: Sync Back to Salesforce

When ready, sync the Leads sheet to update Salesforce records with new owners and statuses.

Using the Workflow

1. Open the workbook from a template button or saved workbook
2. Review new leads in the filtered view
3. Select owners from the dropdown (automatically stores Contact Id)
4. Update status as needed
5. Click Sync to push changes back to Salesforce
6. Changes update Lead.OwnerId and Lead.Status in seconds

Benefits

• Speed: Bulk assignments happen in minutes instead of hours
• Security: All data stays within Salesforce org boundaries
• Reproducibility: Save as template for consistent process
• Visibility: Conditional formatting provides instant visual feedback
• Data Integrity: Tags with Id fields ensure proper record relationships

Additional Patterns

ONCE for Timestamps

Record when a row was created:

=ONCE(NOW())

This evaluates once when the formula is entered, then preserves the timestamp.

ONCHANGE for Conditional Updates

Update a last-modified timestamp only when another cell changes:

=ONCHANGE(NOW(), A1)

The timestamp updates only when cell A1 is modified.

Combining PICK and FILTER

Extract specific columns from filtered data (assuming Status is in column D):

=PICK(FILTER(Data!A:Z, COL[D] = "Active"), "Name", "Email", "Amount")

This filters the data first, then picks only the columns you need.