Book 20min intro call

Next slot: Tomorrow

GUIDES / Airtable

Airtable formulas & Automation scripts

Our friendly collection of formulas and javascript templates that you'll copy and paste time and time again.

Replace last comma with "and" (Oxford comma)

Formula

Lookups · Regex · Strings


SUBSTITUTE(
  IF(
    FIND(
      ",",
      ARRAYJOIN({Array})
    ),
    REGEX_REPLACE(
      ARRAYJOIN({Array}),
      ",([^,]+)$",
      " and "
    ) & 
    REGEX_EXTRACT(
      ARRAYJOIN({Array}),
      ",([^,]+)$"
    ),
    ARRAYJOIN({Array})
  ),
  ",",
  ", "
)

Date status (Active/Inactive)

Formula

Dates

IF(
  AND(
    {Start date}, 
    {End date}
  ),
  IF(
    OR(
      IS_BEFORE({End date}, TODAY()), 
      IS_AFTER({Start date}, TODAY())
    ),
    "Inactive",
    "Active"
  )
)

Check if date is weekday

Formula

Dates

AND(
	WEEKDAY({Date}) >= 1, 
	WEEKDAY({Date}) <= 5
)

Convert duration to HH:MM

Formula

Dates & times · Conversion

IF(
  {Start time (manual)},
  RIGHT("0" & FLOOR({Start time (manual)} / 3600), 2) & ":" &
  RIGHT("0" & ROUNDDOWN(MOD({Start time (manual)}, 3600) / 60, 0), 2)
)

Duplicate tree of records from multiple tables

Script

Duplicate · Batch

/**
 * Generic Child Cloner Template
 * Purpose: Copy selected fields from all child records linked to one parent record,
 *     and create new child records linked to a different parent record.
 *
 * Inputs (via input.config()):
 * - originalRecordID (string or [string]) -> parent to copy FROM
 * - cloneRecordID   (string)       -> parent to link TO
 *
 * How to use:
 * 1) Adjust CONFIG: child tables, parent link field name, and the fields to copy.
 * 2) Ensure the parent link field is a Linked record field pointing to your parent table.
 * 3) Run as an Automation Script step.
 */

let inputConfig = input.config();

// Normalize IDs (some automation inputs provide a single-item array)
const originalRecordID = Array.isArray(inputConfig.originalRecordID)
 ? inputConfig.originalRecordID[0]
 : inputConfig.originalRecordID;

const cloneRecordID = inputConfig.cloneRecordID;

console.log(\originalRecordID = ${originalRecordID});
console.log(\cloneRecordID  = ${cloneRecordID});

// ------------------------- CONFIG -------------------------
/**
 * Name of the linked-record field on each child table that points to the parent.
 * Example values: "Parent", "Project", "Order", etc.
 */
const parentLinkFieldName = "Parent";

/**
 * Declare each child table and the fields to copy from it.
 * - tableName: name of the child table in Airtable
 * - fieldsToCopy: array of field names (e.g., "Field 1", "Field 2")
 */
const COPY_SPECS = [
 {
  tableName: "Child Table A",
  fieldsToCopy: ["Field 1", "Field 2", "Field 3"], // e.g., single select, number, linked record
 },
 {
  tableName: "Child Table B",
  fieldsToCopy: ["Field 1", "Field 2", "Field 3", "Field 4"],
 },
 {
  tableName: "Child Table C",
  fieldsToCopy: ["Field 1", "Field 2"],
 },
 {
  tableName: "Child Table D",
  fieldsToCopy: ["Field 1"],
 },
];
// ----------------------- END CONFIG -----------------------


// ---- Utility helpers ----
/** Safely map a cell value to the correct "create" payload */
function mapCellForCreate(value) {
 if (value === null || value === undefined) return null;

 // Linked record(s): keep as array of {id}
 if (Array.isArray(value) && value.every(v => v && typeof v.id === "string")) {
  return value.map(v => ({ id: v.id }));
 }

 // Single select: {name}
 if (value && typeof value === "object" && "name" in value && "color" in value) {
  return { name: value.name };
 }

 // Rich text / long text / text / number / duration / checkbox / etc.:
 return value;
}

/** Chunk an array into batches of size n (Airtable limit: 50) */
function chunk(arr, n = 50) {
 const out = [];
 for (let i = 0; i < arr.length; i += n) out.push(arr.slice(i, i + n));
 return out;
}

/**
 * Clone all child records in a table that are linked to originalRecordID.
 */
async function cloneChildren(table, linkField, fieldsToCopy) {
 const fieldsToSelect = Array.from(new Set([linkField, ...fieldsToCopy]));
 const query = await table.selectRecordsAsync({ fields: fieldsToSelect });

 // Filter: rows linked to the original parent
 const source = query.records.filter(r => {
  const links = r.getCellValue(linkField) || [];
  return links.some(x => x.id === originalRecordID);
 });

 if (source.length === 0) {
  console.log([${table.name}] No records found linked to ${originalRecordID}.);
  return 0;
 }

 // Prepare create payloads
 const creates = [];
 for (const r of source) {
  const fields = {};
  for (const f of fieldsToCopy) {
   const raw = r.getCellValue(f);
   const mapped = mapCellForCreate(raw);
   if (mapped !== null) fields[f] = mapped;
  }
  // Relink to the new parent
  fields[linkField] = [{ id: cloneRecordID }];
  creates.push({ fields });
 }

 // Batch create
 let createdCount = 0;
 for (const batch of chunk(creates, 50)) {
  const res = await table.createRecordsAsync(batch);
  createdCount += res.length;
 }

 console.log([${table.name}] Cloned ${createdCount} record(s).);
 return createdCount;
}

(async () => {
 // Select all tables early and reuse them
 const tablesByName = Object.fromEntries(
  base.tables.map(t => [t.name, t])
 );

 let total = 0;
 for (const spec of COPY_SPECS) {
  const table = tablesByName[spec.tableName];
  if (!table) {
   console.log([WARN] Table not found: "${spec.tableName}". Skipping.);
   continue;
  }

  total += await cloneChildren(table, parentLinkFieldName, spec.fieldsToCopy);
 }

 console.log(\Completed. Total cloned child records: ${total}.);
})();

Regex extract text from between emoji

Formula

Strings · Linked records · Automation triggers & utilities

IF(
    FIND(
      "👩‍🍳",
      {Name}
    ),
    TRIM(REGEX_EXTRACT({Name}, '👩‍🍳(.*?)👩‍🍳'))
  )IF(
    FIND(
      "👩‍🍳",
      {Name}
    ),
    TRIM(REGEX_EXTRACT({Name}, '👩‍🍳(.*?)👩‍🍳'))
  )

Remove leading and trailing commas ", " from string

Formula

Regex · Strings

REGEX_REPLACE(
 REGEX_REPLACE({Text}, "^(,\\\\s*)+", ""),
 "(,\\\\s*)+$", ""
)

Count unique (include blanks)

Formula

Rollups

COUNTALL(ARRAYUNIQUE(values))

Return largest of dates from fields

Formula

Dates & times

DATETIME_PARSE(
MAX(
  IF({Date 1}, VALUE(DATETIME_FORMAT({Date 1}, "X")), 0),
  IF({Date 2}, VALUE(DATETIME_FORMAT({Date 2}, "X")), 0),
  IF({Date 3}, VALUE(DATETIME_FORMAT({Date 3}, "X")), 0)
), "X")

Delay automation by X seconds

Script

Utility

function delay(seconds) {
    const startTime = Date.now()
    while (Date.now() - startTime < seconds * 1000)
        continue
}

delay(5)

Has trailing space?

Formula

Strings

IF(
  RIGHT(Name, 1) = " ",
  "Yes"
)

Fuzzy compare two strings ignoring spaces and commas

Formula

Strings

REGEX_REPLACE({Period months} & "", "[,\\s]+", "") != REGEX_REPLACE({= Period months} & "", "[,\\s]+", ""),

Date status (Completed/Current/Future)

Formula

Dates & times

IF(
 AND({Start Date}, {End Date}),
 IF(
  IS_BEFORE({End Date}, TODAY()), 
  "Completed",
  IF(
    IS_AFTER({Start Date}, TODAY()),
   "Future",
   "Current"
  )
 )
)

Currency string (£10.00)

Formula

Currency

IF(
LEFT({Total}&"",1,1)='-',
'-',
'')&'$'&
IF(
ABS(VALUE({Total}&""))>=1000000,
INT(ABS(VALUE({Total}&""))/1000000)&','&
IF(
INT(MOD(VALUE({Total}&""),1000000)/1000)<100,
REPT('0',3-LEN(INT(MOD(VALUE({Total}&""),1000000)/1000)&"")),
'')&INT(MOD(VALUE({Total}&""),1000000)/1000)&','&
IF(
INT(MOD(VALUE({Total}&""),1000))<100,
REPT('0',3-LEN(INT(MOD(VALUE({Total}&""),1000))&"")),
'')&INT(MOD(VALUE({Total}&""),1000)),
IF(
ABS(VALUE({Total}&""))>=1000,
INT(ABS(VALUE({Total}&""))/1000)&','&
IF(
INT(MOD(VALUE({Total}&""),1000))<100,
REPT('0',3-LEN(INT(MOD(VALUE({Total}&""),1000))&"")),
'')&INT(MOD(VALUE({Total}&""),1000)),
INT(ABS(VALUE({Total}&"")))))&'.'&
IF(
LEN(ROUND(MOD(VALUE({Total}&""),1)*100,0)&"")<2,
'0',
''
)&
ROUND(MOD(VALUE({Total}&""),1)*100)

Create a project in Harvest

Script

Harvest · API · Fetch · POST

let inputConfig = input.config();

const accountId = '123123123'; // Replace with your Harvest account ID
const accessToken = '123123123123'; // Replace with your Harvest access token

const apiUrl = 'https://api.harvestapp.com/v2/projects'; // Updated to projects endpoint

// New project data
const newProjectData = {
    client_id: inputConfig.projectClientHarvestID, // Replace with the Harvest ID of the client
    name: inputConfig.projectName,
    code: inputConfig.projectJobCode,
    is_billable: true,
    bill_by: "Tasks",
    budget_by: "project_cost",
    starts_on: new Date()
    // Add other project properties as needed
};

let createdProjectId;

// Create a new project in Harvest
try {
  const response = await fetch(apiUrl, {
    method: 'POST',
    headers: {
      'Authorization': Bearer ${accessToken},
      'Harvest-Account-ID': accountId,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify(newProjectData),
  });

  if (!response.ok) {
    throw new Error(Error creating new project in Harvest: ${response.statusText});
  }

  const createdProject = await response.json();

  createdProjectId = createdProject.id;

  console.log('New Project created in Harvest:', createdProject);

} catch (error) {
  console.error('Error creating new project in Harvest:', error);
}

Send webhook to Make

Script

Make · Webhook · Fetch

let inputConfig = input.config();
let webhook = 'https://hook.eu2.make.com/…';
let call = await fetch(\${webhook}?recordID=${inputConfig.recordID});
console.log(call);

Calculate # of overlapping days

Formula

Dates & times

IF(
    AND(
        {Range 1 (Start date)} <= {Range 2 (Start date)},
        {Range 1 (End date)} >= {Range 2 (End date)}
    ),
    {# days (from Month)},
    IF(
        AND(
            {Range 2 (Start date)} <= {Range 1 (Start date)},
            {Range 2 (End date)} >= {Range 1 (End date)}
        ),
        {# days (from Project periods)},
        IF(
            AND(
                {Range 2 (Start date)} <= {Range 1 (Start date)},
                {Range 2 (End date)} <= {Range 1 (End date)}
            ),
            DATETIME_DIFF({Range 2 (End date)}, {Range 1 (Start date)}, 'days') + 1,
            IF(
                AND(
                    {Range 2 (End date)} >= {Range 1 (End date)},
                    {Range 2 (Start date)} <= {Range 1 (End date)}
                ),
                DATETIME_DIFF({Range 1 (End date)}, {Range 2 (Start date)}, 'days') + 1
            )
        )
    )
)

Compare comma-separated string to linked record

Formula

Strings · Linked records · Regex

IF(
  REGEX_REPLACE({Buckets}, "[, ]", "") != REGEX_REPLACE({= Buckets}, "[, ]", ""),
"Yes"
)

Link or unlink to junction

Formula

Automation triggers & utilities

IF(
  OR(
    {Field: String or select etc} != {Field: Linked record to junction table}
  ),
  "Yes"
)

Previous record ID from this record in array

Formula

Lookups

IF(
  {All record IDs from a parent},
  IF(
    AND(
      "/* Has one ID before it */",
      FIND({This record ID}, {All record IDs from a parent}) >= 18
    ),
    MID({All record IDs from a parent}, FIND({This record ID}, {All record IDs from a parent}) - 19, 17)
  )
)

Assumes that {All record IDs from a parent} is comma-separated with a space, e.g. rec5Elj0JybN4NvFh, recMFGrwGr7LfUkDo If not, use -17 instead of -19.

Financial year (UK) as string

Formula

Dates & times

IF(
    VALUE(DATETIME_FORMAT({Date}, "MMDD")) >= 406,
    DATETIME_FORMAT({Date}, "YYYY") & "/" & (VALUE(DATETIME_FORMAT({Date}, "YYYY")) + 1),
    (VALUE(DATETIME_FORMAT({Date}, "YYYY")) - 1) & "/" & DATETIME_FORMAT({Date}, "YYYY")
)

Merge arrays and return unique values

Formula

Arrays · Rollups

ARRAYUNIQUE(ARRAYFLATTEN(values))

Must be a rollup of an **array** (lookup or rollup).

% through dates

Formula

Dates & times

SWITCH(
  {Date status},
  "Completed",
  1,
  "Current",
  DAY(TODAY()) / {# days},
  "Future",
  0
)

Count unique (don't include blanks)

Formula

Rollups

COUNTA(ARRAYUNIQUE(values))

Date range as string

Formula

Dates

IF(
  AND(
    {Date 1} != "",
    {Date 2} != ""
  ),
  DATETIME_FORMAT({Date 1}, "DD/M/YYYY") & 
  " → " & DATETIME_FORMAT({Date 2}, "DD/M/YYYY"),
  IF(
    {Date 1} != "",
    DATETIME_FORMAT({Date 1}, "DD/M/YYYY"),
    IF(
      {Date 2} != "",
      DATETIME_FORMAT({Date 2}, "DD/M/YYYY")
    )
  )
)

Weeks from this week

Formula

Dates & times

ROUND((DATETIME_FORMAT({Start date}, 'X') - DATETIME_FORMAT(DATEADD(TODAY(), 1-IF(WEEKDAY(TODAY())=0, 7, WEEKDAY(TODAY())), 'day'), 'X')) / 60 / 60 / 24 / 7)

UNIX timestamp as days (Xd)

Formula

Dates & times

ROUNDDOWN(VALUE(DATETIME_FORMAT({Datetime}, 'X')) / 60 / 60 / 24, 0)

Compare strings with different types

Formula

Strings · Regex

REGEX_REPLACE(SUBSTITUTE({Field 1}, ' ', ''), '[^a-zA-Z0-9]', '') != REGEX_REPLACE(SUBSTITUTE({Field 2}, ' ', ''), '[^a-zA-Z0-9]', '')

Currency string (£10)

Formula

Currency

"£" & IF(LEN(ROUND({Value}, 0) & "")<=3,ROUND({Value}, 0) & "",IF(LEN(ROUND({Value}, 0) & "")<=6,CONCATENATE(LEFT(ROUND({Value}, 0) & "",LEN(ROUND({Value}, 0) & "")-3),",",MID(ROUND({Value}, 0) & "",LEN(ROUND({Value}, 0) & "")-2,3)),CONCATENATE(LEFT(ROUND({Value}, 0) & "",LEN(ROUND({Value}, 0) & "")-6),",",MID(ROUND({Value}, 0) & "",LEN(ROUND({Value}, 0) & "")-5,3),",",MID(ROUND({Value}, 0) & "",LEN(ROUND({Value}, 0) & "")-2,3))))

Fuzzy compare strings by a-z09

Formula

Strings

**A#**
"#" & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "a", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "b", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "c", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "d", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "e", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "f", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "g", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "h", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "i", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "j", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "k", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "l", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "m", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "n", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "o", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "p", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "q", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "r", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "s", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "t", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "u", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "v", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "w", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "x", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "y", ""))) & 
(LEN(LOWER({A})) - LEN(SUBSTITUTE(LOWER({A}), "z", ""))) & 
(LEN({A}) - LEN(SUBSTITUTE({A}, "0", ""))) & 
(LEN({A}) - LEN(SUBSTITUTE({A}, "1", ""))) & 
(LEN({A}) - LEN(SUBSTITUTE({A}, "2", ""))) & 
(LEN({A}) - LEN(SUBSTITUTE({A}, "3", ""))) & 
(LEN({A}) - LEN(SUBSTITUTE({A}, "4", ""))) & 
(LEN({A}) - LEN(SUBSTITUTE({A}, "5", ""))) & 
(LEN({A}) - LEN(SUBSTITUTE({A}, "6", ""))) & 
(LEN({A}) - LEN(SUBSTITUTE({A}, "7", ""))) & 
(LEN({A}) - LEN(SUBSTITUTE({A}, "8", ""))) & 
(LEN({A}) - LEN(SUBSTITUTE({A}, "9", "")))

**B#**
"#" & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "a", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "b", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "c", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "d", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "e", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "f", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "g", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "h", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "i", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "j", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "k", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "l", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "m", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "n", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "o", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "p", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "q", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "r", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "s", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "t", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "u", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "v", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "w", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "x", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "y", ""))) & 
(LEN(LOWER({B})) - LEN(SUBSTITUTE(LOWER({B}), "z", ""))) & 
(LEN({B}) - LEN(SUBSTITUTE({B}, "0", ""))) & 
(LEN({B}) - LEN(SUBSTITUTE({B}, "1", ""))) & 
(LEN({B}) - LEN(SUBSTITUTE({B}, "2", ""))) & 
(LEN({B}) - LEN(SUBSTITUTE({B}, "3", ""))) & 
(LEN({B}) - LEN(SUBSTITUTE({B}, "4", ""))) & 
(LEN({B}) - LEN(SUBSTITUTE({B}, "5", ""))) & 
(LEN({B}) - LEN(SUBSTITUTE({B}, "6", ""))) & 
(LEN({B}) - LEN(SUBSTITUTE({B}, "7", ""))) & 
(LEN({B}) - LEN(SUBSTITUTE({B}, "8", ""))) & 
(LEN({B}) - LEN(SUBSTITUTE({B}, "9", "")))

**Compare**
IF(
  {A #} != {B #},
  "Mismatch",
  "Match"
)

Used as an approximation to determine if two strings are the same with an alphabet comparison, e.g. two sentences, addresses etc.

Get all projects from Harvest

Script

Harvest · API · Fetch · Loop · GET

const accountId = '123123'; // Replace with your Harvest account ID
const accessToken = '123123123123'; // Replace with your Harvest access token

const apiUrl = 'https://api.harvestapp.com/v2/projects';

let harvestProjects;

// Fetch all projects from Harvest
fetch(apiUrl, {
  method: 'GET',
  headers: {
    'Authorization': Bearer ${accessToken},
    'Harvest-Account-ID': accountId,
    'Content-Type': 'application/json',
  },
})
  .then(response => {
    if (!response.ok) {
      throw new Error(Error fetching projects from Harvest: ${response.statusText});
    }
    return response.json();
  })
  .then(data => {
    console.log('List of Projects:', data.projects);
    harvestProjects = data.projects;
  })
  .catch(error => {
    console.error('Error fetching projects from Harvest:', error);
  });

Find record # in linked record IDs

Formula

Linked records

((FIND({Invoice record ID}, {Client invoice record IDs list} & "") - 1)/ 19) + 1

Batch delete records

Script

Delete · Batch · Arrays

let inputConfig = input.config(); 
let table = base.getTable("Table"); 

while (inputConfig.recordIDs.length > 0) { 
	await table.deleteRecordsAsync(inputConfig.recordIDs.slice(0, 50)); 
	inputConfig.recordIDs = inputConfig.recordIDs.slice(50); 
}

Filter selectRecordsAsync()

Script

Filter · Select records

let periodsQuery = await periodsViews.selectRecordsAsync({
    fields: ["Weeks from This Week"]
});

const rangeOfPeriodsIDs = periodsQuery.records
    .filter(record =>  
        record.getCellValue('Weeks from This Week') >= inputConfig.StartDateWeeksFromThisWeek &&
        record.getCellValue('Weeks from This Week') <= inputConfig.EndDateWeeksFromThisWeek
        // console.log(record.getCellValue('Weeks from This Week'))
        // return ""
    )
    .map(record => 
        // get cell value of Period column
        record.id
    );


**Example 2**

const existingPeriodsIDs = scheduledQuery.records
    .filter(record => 
        record.getCellValueAsString('Project') == inputConfig.ProjectName.toString() && 
        record.getCellValueAsString('Member') == inputConfig.MemberName.toString()
    )
    .map(record => 
        // get cell value of Period column
        record.getCellValue('Period')[0].id
    );

First workday of this month

Formula

Dates & times · Utility

SWITCH(
  WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT(TODAY(), "YYYY-MM") & "-01")),
  0, DATEADD(DATETIME_PARSE(DATETIME_FORMAT(TODAY(), "YYYY-MM") & "-01"), 1, 'days'),
  6, DATEADD(DATETIME_PARSE(DATETIME_FORMAT(TODAY(), "YYYY-MM") & "-01"), 2, 'days'),
  DATETIME_PARSE(DATETIME_FORMAT(TODAY(), "YYYY-MM") & "-01")
)

Random 4 digit number from record ID

Formula

Strings · Numbers · Placeholder · Random

VALUE(
  CONCATENATE(
    SWITCH(
      MID(UPPER(RECORD_ID()), LEN(RECORD_ID()) - 3, 1),
      "A", 1, "B", 1,
      "C", 2, "D", 2,
      "E", 3, "F", 3,
      "G", 4, "H", 4,
      "I", 5, "J", 5,
      "K", 6, "L", 6,
      "M", 7, "N", 7,
      "O", 8, "P", 8,
      "Q", 9, "R", 9,
      "S", 1, "T", 1,
      "U", 2, "V", 2,
      "W", 3, "X", 3,
      "Y", 4, "Z", 4,
      "1"
    ),
    SWITCH(
      MID(UPPER(RECORD_ID()), LEN(RECORD_ID()) - 2, 1),
      "A", 1, "B", 1,
      "C", 2, "D", 2,
      "E", 3, "F", 3,
      "G", 4, "H", 4,
      "I", 5, "J", 5,
      "K", 6, "L", 6,
      "M", 7, "N", 7,
      "O", 8, "P", 8,
      "Q", 9, "R", 9,
      "S", 1, "T", 1,
      "U", 2, "V", 2,
      "W", 3, "X", 3,
      "Y", 4, "Z", 4,
      "1"
    ),
    SWITCH(
      MID(UPPER(RECORD_ID()), LEN(RECORD_ID()) - 1, 1),
      "A", 1, "B", 1,
      "C", 2, "D", 2,
      "E", 3, "F", 3,
      "G", 4, "H", 4,
      "I", 5, "J", 5,
      "K", 6, "L", 6,
      "M", 7, "N", 7,
      "O", 8, "P", 8,
      "Q", 9, "R", 9,
      "S", 1, "T", 1,
      "U", 2, "V", 2,
      "W", 3, "X", 3,
      "Y", 4, "Z", 4,
      "1"
    ),
    SWITCH(
      MID(UPPER(RECORD_ID()), LEN(RECORD_ID()), 1),
      "A", 1, "B", 1,
      "C", 2, "D", 2,
      "E", 3, "F", 3,
      "G", 4, "H", 4,
      "I", 5, "J", 5,
      "K", 6, "L", 6,
      "M", 7, "N", 7,
      "O", 8, "P", 8,
      "Q", 9, "R", 9,
      "S", 1, "T", 1,
      "U", 2, "V", 2,
      "W", 3, "X", 3,
      "Y", 4, "Z", 4,
      "1"
    )
  )
)

Extract value from key in JSON

Formula

Strings · Utility

MID(
  {🤖 Data},
  FIND('"jsonKey":"', {🤖 Data}) + LEN('"jsonKey":"'),
  FIND('"', {🤖 Data}, FIND('"jsonKey":"', {🤖 Data}) + LEN('"jsonKey":"')) - (FIND('"jsonKey":"', {🤖 Data}) + LEN('"jsonKey":"'))
)

e.g. {"name":"Alice","email":"alice@example.com"} returns alice@example.com

Regex subtract one list from another

Formula

Strings · Regex

IF(
  {Include},
  IF(
    {Exclude} = "",
    {Include},
    REGEX_REPLACE(
      {Include},
      "(?:\\b" & SUBSTITUTE({Exclude}, ", ", "\\b|\\b") & "\\b)",
      ""
    )
  )
)

Merge or Union two comma-separated arrays

Formula

Strings · Arrays · Regex

IF( {Linked Category years}, IF( {Backfilled category years}, {Linked Category years} & IF( TRIM( REGEX_REPLACE( {Backfilled category years}, "(?:\\b" & SUBSTITUTE({Linked Category years}, ", ", "\\b|\\b") & "\\b(?:, )?)", "" ) ) = "", "", ", " & TRIM( REGEX_REPLACE( {Backfilled category years}, "(?:\\b" & SUBSTITUTE({Linked Category years}, ", ", "\\b|\\b") & "\\b(?:, )?)", "" ) ) ), {Linked Category years} ), {Backfilled category years} )

Update record with all of another record's field values

Script

Update · Duplicate

let inputConfig = input.config();

// Initialize the table object
let table = base.getTable("Law versions");

// Retrieve the specific record by its ID using selectRecordAsync
let previousRecord = await table.selectRecordAsync(inputConfig.previousRecordID);

// Ensure the record exists
if (!previousRecord) {
    throw new Error(Record with ID ${inputConfig.previousRecordID} not found.);
}

// Initialize an object to hold the fields to copy
let fieldsToCopy = {};

// Loop over the table's fields
for (let field of table.fields) {
    let fieldName = field.name;
    // Check if the field name does not start with '🔴' and is not a computed field
    if (!fieldName.startsWith('🔴') && !field.isComputed) {
        // Use getCellValue to fetch the value from the previousRecord for this field
        let fieldValue = previousRecord.getCellValue(fieldName);
        // Check if the field value is not undefined or null before adding it to fieldsToCopy
        if (fieldValue !== undefined && fieldValue !== null) {
            fieldsToCopy[fieldName] = fieldValue;
        }
    }
}

console.log(fieldsToCopy);

// Update the existing record with the fields to copy
await table.updateRecordAsync(inputConfig.newRecordID, fieldsToCopy);

Sanitise email addresses

Formula

Strings

LOWER(REGEX_REPLACE(TRIM(Email), "[^a-zA-Z0-9@._%+-]", ""))

Get all clients from Harvest

Script

Harvest · API · Fetch · GET

const accountId = '712104'; // Replace with your Harvest account ID
const accessToken = '1058395.pt.QPQnB8ZhjlezcRZyjILP_YFGiCm_JxvQfJtykfV3duQpbjgtCgA1DXcJd4wb3w56ABugZzPcbO7Ie5GooQjpiQ'; // Replace with your Harvest access token

const apiUrl = https://api.harvestapp.com/v2/clients;

// Fetch Harvest clients
let harvestClients;
try {
  const response = await fetch(apiUrl, {
    method: 'GET',
    headers: {
      'Authorization': Bearer ${accessToken},
      'Harvest-Account-ID': accountId,
      'Content-Type': 'application/json',
    },
  });

  if (!response.ok) {
    throw new Error(Error fetching Harvest clients: ${response.statusText});
  }

  const data = await response.json();
  harvestClients = data.clients;
  console.log('List of Clients:', harvestClients);
} catch (error) {
  console.error('Error fetching clients:', error);
}

// Fetch Airtable clients
let clientsTable = base.getTable("Clients");
let allClientsRecords = await clientsTable.selectRecordsAsync({
  fields: ["Name", "Harvest ID", "Harvest name", "Address"]
});

// Convert Airtable records to an array
let allClients = allClientsRecords.records.map(record => ({
  id: record.id,
  name: record.getCellValueAsString("Name").toLowerCase(),
  harvestID: record.getCellValueAsString("Harvest ID"),
  harvestName: record.getCellValueAsString("Harvest name"),
  address: record.getCellValueAsString("Address"),
}));

let recordsToUpdate = [];

// Loop through the Harvest clients
harvestClients.forEach(harvestClient => {
  const harvestClientName = harvestClient.name;
  
  // Find a matching Airtable client by name
  const matchingAirtableClient = allClients.find(airtableClient => {
    // Use a case-insensitive comparison for names
    return airtableClient.name === harvestClientName.toLowerCase();
  });

  if (matchingAirtableClient) {

    recordsToUpdate.push({
        id: matchingAirtableClient.id,
        fields: {
          "Harvest ID": harvestClient.id.toString(), // Assuming 'id' is a string in Airtable
          "Harvest name": harvestClientName,
          "Address": harvestClient.address
        }
    });

  }
});

console.log(recordsToUpdate);

// Update records with Hourly rate = 0
while (recordsToUpdate.length > 0) {
    await clientsTable.updateRecordsAsync(recordsToUpdate.slice(0, 50));
    recordsToUpdate = recordsToUpdate.slice(50);
}

Update custom field in Asana

Script

Update · API · Asana

const key = "a";

let inputConfig = input.config();
let asanaFinanceTaskID = inputConfig.asanaFinanceTaskID.toString();
let teamNameString = inputConfig.teamNameString.toString();

// Update custom field on the Asana task
const updateAsanaTaskCustomField = (asanaGid) => {
  const options = {
    method: 'PUT',
    headers: {
      accept: 'application/json',
      'content-type': 'application/json',
      authorization: 'Bearer ' + key,
    },
    body: JSON.stringify({
      data: {
        custom_fields: {
          '1206877936413068': teamNameString, // Asana finance URL
        },
      },
    }),
  };

  return fetch('https://app.asana.com/api/1.0/tasks/' + asanaGid, options)
    .then(response => response.json())
    .then(response => {
      console.log(response);
      return asanaGid; // Return the GID
    })
    .catch(err => {
      console.error(err);
      return null;
    });
};

// Execute the update
updateAsanaTaskCustomField(asanaFinanceTaskID)
  .then(asanaGid => {
    if (asanaGid) {
      console.log('Asana task updated successfully:', asanaGid);
    } else {
      throw new Error('Failed to update the Asana task');
    }
  })
  .catch(err => console.error(err));

Bulk assign users to Harvest

Script

Harvest · API · Fetch · POST

let inputConfig = input.config();

if(
  inputConfig.activeUserIDs.length > 0
)

  {

  // Assuming projectID is the ID of the project created in Script 1
  const projectID = inputConfig.projectID;

  // Assuming activeUserIDs is the array of active user IDs from Script 2
  // const activeUserIDs = ['user_id_1', 'user_id_2', 'user_id_3', /* ... */];
  const activeUserIDs = inputConfig.activeUserIDs;

  // Harvest API endpoint for assigning a user to a project
  const assignUserEndpoint = 'https://api.harvestapp.com/v2/projects/' + inputConfig.projectID + '/user_assignments';

  // Harvest API access toke
  const accountId = '712104'; // Replace with your Harvest account ID
  const accessToken = '1058395.pt.QPQnB8ZhjlezcRZyjILP_YFGiCm_JxvQfJtykfV3duQpbjgtCgA1DXcJd4wb3w56ABugZzPcbO7Ie5GooQjpiQ'; // Replace with your Harvest access token

  // Function to assign a user to the project
  function assignUserToProject(userID) {
    const payload = {
      method: 'post',
      contentType: 'application/json',
      headers: {
        'Authorization': Bearer ${accessToken},
        'Harvest-Account-ID': accountId,
        'Content-Type': 'application/json',
        'Accept': 'application/json',
      },
      body: JSON.stringify({
        project_id: projectID,
        user_id: userID
      }),
    };

    return fetch(assignUserEndpoint, payload)
      .then(response => response.json())
      .then(responseData => {
        console.log(User ${userID} assigned to project. Response:, responseData);
      })
      .catch(error => {
        console.error(Error assigning user ${userID} to project:, error.message);
      });
  }

  function assignAllUsersToProject() {
    // Map each user assignment promise and wait for all to complete
    Promise.all(activeUserIDs.map(userID => assignUserToProject(userID)))
      .then(() => {
        console.log('All user assignments completed.');
      })
      .catch(error => {
        console.error('Error in user assignments:', error.message);
      });
  }

  assignAllUsersToProject();

}

Random 1 digit number from record ID

Formula

Strings · Numbers · Placeholder

VALUE(
  CONCATENATE(
    SWITCH(
      MID(UPPER(RECORD_ID()), LEN(RECORD_ID()) - 1, 1),
      "A", 1, "B", 1,
      "C", 2, "D", 2,
      "E", 3, "F", 3,
      "G", 4, "H", 4,
      "I", 5, "J", 5,
      "K", 6, "L", 6,
      "M", 7, "N", 7,
      "O", 8, "P", 8,
      "Q", 9, "R", 9,
      "S", 1, "T", 1,
      "U", 2, "V", 2,
      "W", 3, "X", 3,
      "Y", 4, "Z", 4,
      "1"
    )
  )
)

Is today?

Formula

Dates & times

IF(
  Date,
  IF(
    DATETIME_FORMAT({Date}, "DD-MM-YYYY") = DATETIME_FORMAT(TODAY(), "DD-MM-YYYY"),
    "Yes"
  )
)

Merge arrays and return unique count

Formula

Arrays · Rollups

COUNTALL(ARRAYUNIQUE(ARRAYFLATTEN(values)), ", ")

Must be a rollup of an **array** (lookup or rollup).

Get all active users from Harvest

Script

Harvest · API · Fetch · Loop · GET

const accountId = '123123'; // Replace with your Harvest account ID
const accessToken = '123123123123123123123123'; // Replace with your Harvest access token

// https://help.getharvest.com/api-v2/projects-api/projects/user-assignments/

const apiUrl = 'https://api.harvestapp.com/v2/users';

let harvestUsers;

let activeUsers = [];

// Fetch all projects from Harvest
fetch(apiUrl, {
  method: 'GET',
  headers: {
    'Authorization': Bearer ${accessToken},
    'Harvest-Account-ID': accountId,
    'Content-Type': 'application/json',
  },
})
  .then(response => {
    if (!response.ok) {
      throw new Error(Error fetching projects from Harvest: ${response.statusText});
    }
    return response.json();
  })
  .then(data => {
    // console.log('List of Users:', data.users);
    harvestUsers = data.users;

    let activeUsers = harvestUsers.filter(user => user.is_active === true);
    console.log(activeUsers);

    const activeUserIDs = activeUsers.map(user => user.id);

    console.log(activeUserIDs);

    // Assuming you want to split the array into three sets
    const activeUserIDs1 = activeUserIDs.slice(0, 50);
    const activeUserIDs2 = activeUserIDs.slice(50, 100);
    const activeUserIDs3 = activeUserIDs.slice(100, 150);

    // Output the sets with appropriate keys
    output.set('Active User IDs 1', activeUserIDs1);
    output.set('Active User IDs 2', activeUserIDs2);
    output.set('Active User IDs 3', activeUserIDs3);
  })
  .catch(error => {
    console.error('Error fetching projects from Harvest:', error);
  });

Select records by set of IDs

Script

Loop · Select records

let table = base.getTable("Table");
let records = await budgetItemsTable.selectRecordsAsync(
    {
        fields: [
            "Title"
        ],
        recordIds: inputConfig.recordIds
    }
);

Random 1 digit number

Formula

Random · Numbers · Strings · Placeholder

VALUE(
  CONCATENATE(
    SWITCH(
      MID(UPPER(RECORD_ID()), LEN(RECORD_ID()) - 1, 1),
      "A", 1, "B", 1,
      "C", 2, "D", 2,
      "E", 3, "F", 3,
      "G", 4, "H", 4,
      "I", 5, "J", 5,
      "K", 6, "L", 6,
      "M", 7, "N", 7,
      "O", 8, "P", 8,
      "Q", 9, "R", 9,
      "S", 1, "T", 1,
      "U", 2, "V", 2,
      "W", 3, "X", 3,
      "Y", 4, "Z", 4,
      "1"
    )
  )
)

Combine records IDs from one or more Find record actions and set as output

Script

Arrays

let inputConfig = input.config();

let firstRecordIDs = inputConfig.firstRecordIDs || [];
let secondRecordIDs = inputConfig.secondRecordIDs || [];

// Merge and filter unique values
let mergedRecordIDs = [...new Set([...firstRecordIDs, ...secondRecordIDs])];

// Log for debugging
console.log("Merged Unique Record IDs:", mergedRecordIDs);

// Set as output
output.set("mergedRecordIDs", mergedRecordIDs);

Great for setting an output for use in Airtable's Repeating Groups.

Batch 50 max

Script

Batch · Create · Update · Arrays

while (newPSMs.length > 0) {
  await projectSpecialismMonthsTable.createRecordsAsync(newPSMs.slice(0, 50));
  newPSMs = newPSMs.slice(50);
}

Years from this year

Formula

Dates

VALUE(DATETIME_FORMAT({Date}, "YYYY")) - VALUE(DATETIME_FORMAT(TODAY(), "YYYY"))

TOTODAY()

Formula

Dates

IF(
  {Date},
  IF(
    DATETIME_DIFF(TODAY(), {Date}, 'days') = 0,
    "Today",
    IF(
      DATETIME_DIFF(TODAY(), {Date}, 'days') = 1,
      "1 day ago",
      IF(
        DATETIME_DIFF(TODAY(), {Date}, 'days') < 7,
        DATETIME_DIFF(TODAY(), {Date}, 'days') & " days ago",
        IF(
          DATETIME_DIFF(TODAY(), {Date}, 'weeks') < 5,
          DATETIME_DIFF(TODAY(), {Date}, 'weeks') & " week" & IF(DATETIME_DIFF(TODAY(), {Date}, 'weeks') > 1, "s", "") & " ago",
          DATETIME_DIFF(TODAY(), {Date}, 'months') & " month" & IF(DATETIME_DIFF(TODAY(), {Date}, 'months') > 1, "s", "") & " ago"
        )
      )
    )
  )
)

Random 3 digit number from record ID

Formula

Strings · Numbers · Placeholder · Random

VALUE(
  CONCATENATE(
    SWITCH(
      MID(UPPER(RECORD_ID()), LEN(RECORD_ID()) - 2, 1),
      "A", 1, "B", 1,
      "C", 2, "D", 2,
      "E", 3, "F", 3,
      "G", 4, "H", 4,
      "I", 5, "J", 5,
      "K", 6, "L", 6,
      "M", 7, "N", 7,
      "O", 8, "P", 8,
      "Q", 9, "R", 9,
      "S", 1, "T", 1,
      "U", 2, "V", 2,
      "W", 3, "X", 3,
      "Y", 4, "Z", 4,
      "1"
    ),
    SWITCH(
      MID(UPPER(RECORD_ID()), LEN(RECORD_ID()) - 1, 1),
      "A", 1, "B", 1,
      "C", 2, "D", 2,
      "E", 3, "F", 3,
      "G", 4, "H", 4,
      "I", 5, "J", 5,
      "K", 6, "L", 6,
      "M", 7, "N", 7,
      "O", 8, "P", 8,
      "Q", 9, "R", 9,
      "S", 1, "T", 1,
      "U", 2, "V", 2,
      "W", 3, "X", 3,
      "Y", 4, "Z", 4,
      "1"
    ),
    SWITCH(
      MID(UPPER(RECORD_ID()), LEN(RECORD_ID()), 1),
      "A", 1, "B", 1,
      "C", 2, "D", 2,
      "E", 3, "F", 3,
      "G", 4, "H", 4,
      "I", 5, "J", 5,
      "K", 6, "L", 6,
      "M", 7, "N", 7,
      "O", 8, "P", 8,
      "Q", 9, "R", 9,
      "S", 1, "T", 1,
      "U", 2, "V", 2,
      "W", 3, "X", 3,
      "Y", 4, "Z", 4,
      "1"
    )
  )
)

Format date as month (from Months sync)

Formula

Dates & times

IF(
{Start date}, 
DATETIME_FORMAT({Start date}, "YY-MM MMM")
)

Remove trailing ", " comma and space at end of string

Formula

Strings · Regex

REGEX_REPLACE({Text}, "(, )$| $", "")

Months from this month

Formula

Dates & times

(12 * (DATETIME_FORMAT({Start Date}, 'YY') - DATETIME_FORMAT(TODAY(), 'YY'))) - (DATETIME_FORMAT(TODAY(), 'MM') - DATETIME_FORMAT({Start Date}, 'MM'))

Diff in minutes

Formula

Dates & times

IF(
  AND(
    {Start (datetime)},
    {End (datetime)}
  ),
  MAX(
    DATETIME_DIFF({End (datetime)}, {Start (datetime)}, 'minutes'),
    0
  )
)

Random 2 digit number from record ID

Formula

Strings · Numbers · Placeholder · Random

VALUE(
  CONCATENATE(
    SWITCH(
      MID(UPPER(RECORD_ID()), LEN(RECORD_ID()) - 1, 1),
      "A", 1, "B", 1,
      "C", 2, "D", 2,
      "E", 3, "F", 3,
      "G", 4, "H", 4,
      "I", 5, "J", 5,
      "K", 6, "L", 6,
      "M", 7, "N", 7,
      "O", 8, "P", 8,
      "Q", 9, "R", 9,
      "S", 1, "T", 1,
      "U", 2, "V", 2,
      "W", 3, "X", 3,
      "Y", 4, "Z", 4,
      "1"
    ),
    SWITCH(
      MID(UPPER(RECORD_ID()), LEN(RECORD_ID()), 1),
      "A", 1, "B", 1,
      "C", 2, "D", 2,
      "E", 3, "F", 3,
      "G", 4, "H", 4,
      "I", 5, "J", 5,
      "K", 6, "L", 6,
      "M", 7, "N", 7,
      "O", 8, "P", 8,
      "Q", 9, "R", 9,
      "S", 1, "T", 1,
      "U", 2, "V", 2,
      "W", 3, "X", 3,
      "Y", 4, "Z", 4,
      "1"
    )
  )
)

Noloco Certified Expert