You can use a table created by a Block as your destination. When using the block as a destination, the primary or "Action" block will loop thru the results of the destination or "List" block and substitute parameters in the Action block with values from the List block. This can be useful for tasks like sending data to dynamically created Sheets or files.
Demo
Sheets
- Select Block as the type
- Select the block from the drop down (you can filter the list by the block name). Your List Block must have the required fields for the destination type (see below).
- Add from the List Block into the Action Block using the same name as the column name in the List Block (keep the parameter type as the default "value"). Make sure there are no spaces in the column name, e.g.Parameters
some_colum
- Run the block! Once it's complete, check your email for the reports.
select '{{email}}' as assigned_to, u.channel, u.plan, u.email as user_email
from dummy.users as u
where 1=1
and u.channel = '{{channel}}'
Required Fields
- Sheets
- type = sheets
- ss_id (long UUID from within the URL of a Sheet)
- sheet
- append
- TRUE
- FALSE (default)
- cell (A1)
- Slack
- channel
- Email Lists
You can use this sheet as a base (just create a copy):
Using a SQLite query as the block
You can define the List block with a SQLite query. Select "Blocks" as the source to use SQLite. For example, to create a new Sheet everyday use:
SELECT
date('now') as dt,
'sheets' as type,
'REPLACE_WITH_YOUR_ID' as ss_id,
date('now') as sheet,
'FALSE' as append
Using INSERT
, UPDATE
, DELETE
statements
You can send rows from one query to a block that performs, for example, INSERT
's. This can helpful if you need to move data from one database to another. You can also use this to grab data from a Sheet and insert it into a database.
- Create a block with "value" . For example:Parameters
- Create your
SELECT
block. You may want to include aLIMIT
while testing. - Select "Block" as the destination, "Loop thru rows" as the type and the block you created above as the block
- Run the block and verify the results in your destination database
INSERT INTO your.users (channel, country, created_on, email)
VALUES ('{{channel}}', '{{country}}', '{{created_on}}', '{{email}}');
SELECT
blockselect s.channel, s.country, s.created_on, s.email
from my.some_other_table as s
limit 2
Case Study
An apparel company wants to add a sample of customers who abandoned their cart to three different Sheets daily. Each PM is responsible for a separate channel and should only see customers from their channel.
- Create a List Block with channels
- Create an action Block
- Select Block as the destination
- Add the channel parameters to the query in the action block
- Run the block