____________________________
On2Air Scripts is no longer being developed. We invite you to check out our other Airtable apps and extensions.
On2Air Forms – Airtable forms, dashboards, and interfaces
On2Air Backups – Automated Airtable backups of your critical data sent to Google Drive, Dropbox, or Box
On2Air Docs – Automated documents using your Airtable data. Generates and PDF and Google file URL and uploads it back to your Airtable base. Uses Google Docs, Sheets, or Slides as the document template.
____________________________
The Airtable Scripting App brings a new level of customization for your company data in Airtable. You can create scripts to do various tasks and functions with the information stored in your base. From custom booking apps to project dependencies or data cleanup, scripts can help you with a better overview of your business information.
When you start using the same scripts or the same snippets of code in multiple Airtable bases, it’s time to organize with a script management tool.
On2Air: Scripts is your script management system to use with the Airtable Scripting App. On2Air: Scripts gives you more control and organization of your scripts. You can use snippets of code in multiple bases and manage multiple scripts from one dashboard.
Table of Contents
Using Scripts in Your Business
Add the code snippets to On2Air
Add the script to Airtable and view the Sales report
Using Scripts for your Finances
Add the Invoices script to On2Air
Add the script to Airtable and view the Open Invoices report
Managing Multiple Scripts for Your Business
In this article
We’re going to use On2Air: Scripts to add multiple scripts to our Sales base and then use the same snippets in our Invoices base. With the snippets saved, you’ll be able to use this as a starting point for many more scripts.
Using Scripts in Your Business
The Scripting App in Airtable allows you to run JavaScript directly in your base to enhance your data, manage tedious tasks, and create features Airtable doesn’t offer.
You can use custom scripts for:
- Financial Dashboards
- Automated Invoices
- Custom Scheduling
- Detailed Reports
- Processing Recurring Orders
- Connecting to an external API
- Data Cleanup
- Data Validation – check for missing information
About the Sales scripts
Your Sales process and pipeline is a constantly changing system. It’s something that needs to be reviewed regularly to ensure you’re always bringing in and closing new leads.
These scripts will give you a:
- Sales Rep Leaderboard
- Snapshot of your Sales Funnel
- Cold Leads Dashboard
- Sales Rep Averages
What you need to begin
Before creating this script, you need three tables in your base – Sales Reps, Leads, and Interactions
Copy our Sales base template in Airtable to get started
You also need to know how to add scripts to your base.
First, we’ll add the Snippet to On2Air
- In On2Air: Scripts, click Snippets in the menu
- Click New Snippet
- Paste the 4 utility function code snippets into the text box
- Rename your Snippet
- Click Save Snippet
- Enable your Snippet
Utility functions code snippets
const getSingleRecord = async (table, id) => {
let records = await table.selectRecordsAsync( )
return records.getRecord(id)
}
const daysBetween = (date1,date2) => {
date1 = new Date(date1)
date2 = new Date(date2)
date1 = Date.UTC(date1.getFullYear(), date1.getMonth(), date1.getDate());
date2 = Date.UTC(date2.getFullYear(), date2.getMonth(), date2.getDate());
var ms = Math.abs(date1-date2);
return Math.floor(ms/1000/60/60/24)
}
const get = (record, field, raw = false, ifNullValue = "-") => record ? (raw ? record.getCellValue(field) : record.getCellValueAsString(field)) : ifNullValue
const getRecords = async (table, filter = null, sorts = []) => {
let records = await table.selectRecordsAsync( {sorts} )
if(filter){
let filtered = []
for(let record of records.records){
if(filter(record)){
filtered.push(record)
}
}
return filtered
}
return records
}
Next, we’ll add each Script to On2Air
- Click Scripts in the menu
- Click New Script
- Click Insert Snippet to Script
- Choose the Utilities Snippet
- Click Insert Snippet
- Paste the Script into the text box below the Snippet
- Rename the Script
- In this one, we added the Sales Funnel script first
- Click Save Script
- Enable your Script
- Repeat for each Script making sure to insert the Snippet in each one
- Add the Sales Leaderboard script
- Add the Cold Leads script
- Add the Sales Rep Averages script
Sales Funnel script
const salesFunnel = async () => {
let leads = await getRecords(tableLeads, record => true)
let funnel = {}
for(let lead of leads){
let status = get(lead,"Status")
if(!funnel[status]){
funnel[status] = {
Status:status,
Count:0,
Amount:0
}
}
funnel[status].Count++
funnel[status].Amount += get(lead,"Amount",true)
}
funnel = Object.values(funnel)
.sort( (a,b) => statuses.indexOf(a.Status) > statuses.indexOf(b.Status) ? 1 : -1)
.map( entry => {
entry.Count = entry.Count.toLocaleString()
entry.Amount = "$" + entry.Amount.toLocaleString()
return entry
})
output.markdown('## Sales Funnel')
output.table(funnel)
}
await salesFunnel()
Sales Rep Leaderboard script
const salesLeaders = async () => { //get won deals let records = await getRecords( tableLeads, record => { return get(record,'Status') == 'Won' }) //group won deals by reps let rankings = {} for(let record of records){ let rep = record.getCellValueAsString('Sales Rep') //if first time, create entry if(!rankings[rep]){ rankings[rep] = {"Sales Rep":rep,Total:0,"Sales Count":0} } rankings[rep].Total += record.getCellValue('Amount') rankings[rep]["Sales Count"]++ } //sort winners and format $ amount let winners = Object.values(rankings) .sort( (a,b) => a.Total > b.Total ? -1 : 1) .map( entry => { entry.Total = "$" + entry.Total.toLocaleString() return entry }) output.markdown('## Top Sales Leaders') output.table(winners) } await salesLeaders()
Cold Leads by Interaction script
const leadsByInteraction = async () => { let leads = await getRecords(tableLeads, record => { return get(record,'Status') !== 'Won' }, [{field:"Last Interaction", direction:"asc"}]) let interactions = await getRecords( tableInteractions ) leads = leads.map( entry => { let lastInteractionDate = get(entry,'Last Interaction') let leadInteractions = get(entry,"Interactions",true) let lastInteraction = null if(leadInteractions){ for(let interaction of leadInteractions){ if(interaction.name === lastInteractionDate){ lastInteraction = interactions.getRecord(interaction.id) break; } } } let lead = { Name: get(entry,'Name'), Amount: get(entry,'Amount'), "Sales Rep": get(entry,'Sales Rep'), Source: get(entry,'Source'), Status: get(entry,'Status'), "Date Opened": get(entry,'Date Opened'), "Last Interaction Date": lastInteractionDate, "Num Interactions": get(entry,'No. of Interactions'), "Interaction Type": get(lastInteraction,'Type'), "Interaction Notes": get(lastInteraction,'Notes'), } return lead }) output.markdown('## Cold Leads By Interaction Date') output.table(leads) } await leadsByInteraction()
Sales Rep Averages script
const repAverages = async () => {
let leads = await getRecords(tableLeads, record => true)
let averages = {}
for(let lead of leads){
let rep = get(lead,'Sales Rep')
if(!averages[rep])averages[rep] ={
rep, Total: 0, Won: 0, Lost: 0, "In progress": 0, "Pitched": 0,
"Won Amount": 0, "Lost Amount": 0, "Pitched Amount": 0, "In progress Amount": 0,
"Close Days":0, "Interactions":0, "Avg Close Days": 0, "Avg Interactions Per Close": 0
}
let status = get(lead,'Status')
let amount = get(lead,'Amount',true)
averages[rep][status]++
averages[rep]['Total']++
averages[rep][status + " Amount"] += amount
if(status == 'Won'){
let start = get(lead,'Date Opened',true)
let end = get(lead,'Date Closed',true)
let interactions = get(lead,'Interactions',true)
averages[rep]['Interactions'] += interactions ? interactions.length : 0
averages[rep]['Close Days'] += daysBetween(start,end)
}
}
averages = Object.values(averages)
.sort( (a,b) => a.rep.localeCompare(b.rep))
.map( entry => {
entry['Avg Close Days'] = entry["Close Days"] ? (entry["Close Days"] / entry["Won"]).toLocaleString() : "-"
entry['Avg Interactions Per Close'] = entry["Interactions"] ? (entry["Interactions"] / entry["Won"]).toLocaleString() : "-"
delete entry['Close Days']
delete entry['Interactions']
for(let s of statuses){
entry[s] = entry[s].toLocaleString()
entry[s + " Amount"] = "$" + entry[s + " Amount"].toLocaleString()
}
return entry
})
output.markdown('## Rep Averages')
output.table(averages)
}
await repAverages()
Then, we’ll create a Block in On2Air
- Click Blocks in the menu
- Click New Block
- Click Add Script to Block
- Select the Sales Funnel script
- Click Add New Block Script
- Again, Click Add Script to Block
- Continue adding each Script until all 4 are added
- Click the Block Script tab
- Paste the code for the Sales variables
- Enable the Block
Sales variables
const tableLeads = base.getTable('Leads')
const tableInteractions = base.getTable('Interactions')
const tableReps = base.getTable('Sales Reps')
const statuses = ["Pitched","In progress","Won","Lost"]
Last, we’ll add the newly generated script to our base
- Click the Export tab in the On2Air Sales Block
- Click Copy to Clipboard to copy the new code
- Open your Sales base in Airtable
- Add the new code to the Scripting base
- Click Run
You now have an amazing new sales report!
- You should see a menu with a button for each of the 4 scripts
- Click each button to see each report!
Using Scripts for Your Finances
Now, maybe you want to use the same utility snippets for another script in your Finances base.
With scripts, you can display a financial dashboard containing revenue, expenses, and accounts receivable, automatically generate and send invoices, regularly view open orders, and complete data validation checks to ensure your information is entered correctly.
This script will be for our Business Finances base. It will calculate and display a table of “Open Invoices”.
Add the Scripts to your Invoices base
- Since the Snippets are already added, you can add the new Open Invoices script in the Scripts section of On2Air: Scripts just like you did the Sales scripts
- Then, insert the Utilities snippet into your new Open Invoices script
Invoices script
const openInvoices = async () => { //get invoices let records = await getRecords( tableInvoices, record => { return get(record,'Status') == 'Open' }) //group invoices let rankings = {} for(let record of records){ let rep = record.getCellValueAsString('Client') //if first time, create entry if(!rankings[rep]){ rankings[rep] = {"Invoices":rep,Total:0,"Total Open":0} } rankings[rep].Total += record.getCellValue('Total') rankings[rep]["Total Open"]++ } //sort and format $ amount let winners = Object.values(rankings) .sort( (a,b) => a.Total > b.Total ? -1 : 1) .map( entry => { entry.Total = "$" + entry.Total.toLocaleString() return entry }) output.markdown('## Open Invoices') output.table(winners) } await openInvoices()
Now, create a Block in On2Air for the Invoices script
- Create another Block in On2Air
- Add your Invoices Script
- Paste the code for the Invoices variables and enable the Block
Invoice Block variables
const tableInvoices = base.getTable('Invoices')
const tableClient = base.getTable('Client')
const statuses = ["Open"]
You should now have an open invoices report!
- You should see a menu with a button called Open Invoices
- Click the button and you now have a table with a list of Open Invoices!
Managing multiple scripts for your business
Scripts in Airtable can eliminate tedious tasks and give you more extensive features in Airtable. When you are able to add the same script or code snippets to multiple bases, you’ll save time and frustration.
On2Air: Scripts is part of a suite of multiple apps to make Airtable better. Start a free trial of On2Air to create advanced forms, automatically backup your bases, and connect your bases to other bases or any other software.
On2Air: Scripts - Script Management App
Do you use scripts in more than 1 base? Do any of your scripts use some of the same code?
Need more control and organization for your scripts?
Scripts in Airtable allows you to add JavaScript code to interact with the data in your base. If you have multiple scripts or you use the same script in multiple bases, it can get messy trying to maintain each one.
With On2Air: Scripts, you can edit, organize, and maintain multiple scripts for multiple bases from a single codebase.
Start a free trial of On2Air: Scripts and all On2Air apps today!
