Guides: Airtable formulas & automation scripts

Lookups · Regex · Strings

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

Regex · Strings

REGEX_REPLACE(
  UPPER({Name),
  "[^0-9BCDFGHJKLMNPQRSTVWXYZ]",
  ""
)

Dates

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

Dates

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

Dates & times · Strings · Duration · Conversion

IF(
  {Decimal Hours},
  FLOOR({Decimal Hours}) & ":" &
  RIGHT("0" & ROUND(60 * MOD({Decimal Hours}, 1)), 2)
)

Dates & times · Conversion · Strings

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

Linked records · Strings · Arrays · Sort · Primary keys

IF(
  {All record IDs},
  IF(
    AND(
      "/* Has one ID before it */",
      FIND({Record ID}, {All record IDs}) >= LEN({Record ID}) + 1
    ),
    LEFT({All record IDs}, FIND({Record ID}, {All record IDs}) - 3)
  )
)

Strings · Linked records · Automation triggers & utilities · Regex

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

Regex · Strings

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

Rollups

COUNTALL(ARRAYUNIQUE(values))

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")

Delete

let inputConfig = input.config();

let table = base.getTable("Table name");

await table.deleteRecordAsync(inputConfig.recordID);

Use Input configuration for your recordID

Regex · Strings · Numbers · IDs · Primary keys

IF(
  LEN(Autonumber & "") < 5,
  REPT("0", 5 - LEN(Autonumber & "")) & Autonumber,
  Autonumber
)

Padded to min 5 numbers, e.g. 231 -> 00231 123456 -> 123456

Utility

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

delay(5)

Strings

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

Strings

REGEX_REPLACE({STRING A} & "", "[,\\\\s]+", "") != REGEX_REPLACE({= STRING B} & "", "[,\\\\s]+", ""),

Dates & times

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

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)

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);
}

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);

Strings · Utility

IF(
  {Encoded},
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(
            SUBSTITUTE(
              SUBSTITUTE(
                SUBSTITUTE(
                  SUBSTITUTE(
                    SUBSTITUTE(
                      SUBSTITUTE(
                        SUBSTITUTE(
                          SUBSTITUTE(
                            SUBSTITUTE(
                              SUBSTITUTE(
                                SUBSTITUTE(
                                  SUBSTITUTE(
                                    {Encoded},
                                    "%20", " "
                                  ),
                                  "%2C", ","
                                ),
                                "%3A", ":"
                              ),
                              "%2F", "/"
                            ),
                            "%40", "@"
                          ),
                          "%22", "\\""
                        ),
                        "%7B", "{"
                      ),
                      "%7D", "}"
                    ),
                    "%25", "%"
                  ),
                  "%5B", "["
                ),
                "%5D", "]"
              ),
              "%26", "&"
            ),
            "%3D", "="
          ),
          "%3F", "?"
        ),
        "%0A", "\n"
      ),
      "%28", "("
    ),
    "%29", ")"
  )
)

Helpful when you are building a URL in a formula but want to preview the output as you work on it.

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
            )
        )
    )
)

Strings · Linked records · Regex

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

Automation triggers & utilities

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

Dates

IF(
  {Date},
  IF(
    IS_BEFORE({Date}, TODAY()),
    "Past",
    IF(
      IS_AFTER({Date}, TODAY()),
      "Future",
      "Today"
    )
  )
)

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.

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")
)

Arrays · Rollups

ARRAYUNIQUE(ARRAYFLATTEN(values))

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

Dates & times

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

Rollups

COUNTA(ARRAYUNIQUE(values))

Arrays · Filter · Sort · Select records

// Input config
let config = input.config();
let tableName = config.tableName || "Freedcamp projects";
let sortField = config.sortField || "Last refreshed timelogs";

// Get table
let table = base.getTable(tableName);
let view = table.getView("🔖 Refresh timelogs");

// Select all records with the target field
let query = await view.selectRecordsAsync({
    fields: [sortField]
});

// Convert records into array with field values
let records = query.records.map(record => {
    return {
        id: record.id,
        sortValue: record.getCellValue(sortField) // could be null/blank or a date
    };
});

// Custom sort: 
// 1) null/blank values first
// 2) then by oldest date
records.sort((a, b) => {
    if (a.sortValue === null && b.sortValue === null) return 0;
    if (a.sortValue === null) return -1;
    if (b.sortValue === null) return 1;
    return new Date(a.sortValue) - new Date(b.sortValue); // earlier date first
});

// Slice top 300
let topRecords = records.slice(0, 300);

// Collect record IDs
let recordIds = topRecords.map(r => r.id);

console.log("Collected record IDs:", recordIds);
output.set("projectRecordIDs", recordIds);

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")
    )
  )
)

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)

Dates & times

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

Very helpful for using an automations' Find Records conditions to check if dates are >= or <= other dates.

Strings · Regex

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

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))))

Strings

**Field 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", "")))

**Field 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 Fields**
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.

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);
  });

Linked records

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

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 · 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
    );

Dates & times

IF(
  {Date},
  DATETIME_DIFF({Date}, TODAY(), 'days')
)

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")
)

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"
    )
  )
)

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

Strings · Regex

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

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 · 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);

Regex

REGEX_REPLACE({Your Field}, "[^A-Za-z]", "")

Strings

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

Harvest · API · Fetch · GET

const accountId = '712104'; // Replace with your Harvest account ID
const accessToken = 'APIKEY'; // 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 · 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));

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 = 'ID'; // Replace with your Harvest account ID
  const accessToken = 'KEY'; // 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();

}

Dates & times

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

Arrays · Rollups

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

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

Loop · Select records

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

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"
    )
  )
)

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 · Create · Update · Arrays

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

Dates

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

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"
        )
      )
    )
  )
)

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"
    )
  )
)

Numbers · Strings

IF(
{Text},
VALUE(REGEX_EXTRACT({Text}, "\\\\d+"))
)

Dates & times

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

Linked records · IDs

IF(
  {Parent's list of record IDs},
  ((FIND(
    RECORD_ID(),
    {Parent's list of record IDs}
  ) - 1) / 18) + 1
)

### Purpose This formula finds the position (item number) of the current record's ID within a comma-separated list of record IDs stored in a field called`{Parent's list of record IDs}`. ### How It Works 1. `IF({Parent's list of record IDs}, ...)` 2. First checks if the `{Parent's list of record IDs}` field has a value. If it's empty, the formula returns nothing. 3. `RECORD_ID()` 4. Gets the unique ID of the current record (e.g., `recABC123xyz4567`). 5. `FIND(RECORD_ID(), {Parent's list of record IDs})` 6. Finds the character position where the current record's ID starts within the parent's list string. 7. `(...) - 1) / 18) + 1` - This arithmetic converts the character position into an item number:Subtracts 1 to make the calculation zero-indexed - Divides by 18 (the length of a record ID, which is 17 characters, plus 1 for the comma separator) - Adds 1 to convert back to a 1-indexed position ### Example If `{Parent's list of record IDs}` contains: `recABC123xyz4567,recDEF456uvw7890,recGHI789rst0123` - The 1st record ID starts at character 1 → returns 1 - The 2nd record ID starts at character 19 → returns 2 - The 3rd record ID starts at character 37 → returns 3 ### Important Note This formula assumes record IDs are separated by commas (with no spaces).

Strings · Regex

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

Strings

IF(
 FIND(",", {Field} & ""),
 TRIM(
  REGEX_EXTRACT(
   {Field} & "",
   ",([^,]+)$"
  )
 )
)

One, Two, Three = Three

Dates & times

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

Dates & times

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

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"
    )
  )
)

Replace last comma with "and" (Oxford comma)

Extract numbers and consonants from string

Date range status (Active/Inactive)

Check if date is weekday

Convert decimal to hh:mm duration string

Convert duration to HH:MM string

Previous record IDs before this record ID

Extract text between characters (emoji)

Remove leading and trailing commas ", " from string

Count unique (include blanks)

Return largest of dates from fields

Delete a single record

Padded Autonumber

Delay automation by X seconds

Check if string has trailing space

Fuzzy compare two strings ignoring spaces and commas

Date range status (Completed/Current/Future)

Currency string (£10.00)

Create a project in Harvest

Send webhook to Make

Unencode a URL encoded string

Calculate # of overlapping days

Compare comma-separated string to linked record

Link or unlink to junction

Date status (Past/Future/Today)

Previous single record ID from this record in array

Financial year (UK) as string

Merge arrays and return unique values

% through dates

Count unique (don't include blanks)

Sort and slice records

Date range as string

Weeks from this week

UNIX timestamp as days (Xd)

Compare strings with different types

Currency string (£10)

Fuzzy compare strings by a-z09

Get all projects from Harvest

Count occurrences of string in list of strings

Batch delete records

Filter selectRecordsAsync()

# days from today (Single date)

First workday of this month

Random 4 digit number from record ID

Extract value from key in JSON

Regex subtract one list from another

Merge or Union two comma-separated arrays

Update record with all of another record's field values

Extract letters from string

Sanitise email addresses

Get all clients from Harvest

Update custom field in Asana

Bulk assign users to Harvest

Is today?

Merge arrays and return unique count

Select records by set of IDs

Random 1 digit number

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

Batch 50 max

Years from this year

TOTODAY()

Random 3 digit number from record ID

Extract numbers from string

Format date as month (from Months sync)

Find record ID position in array of record IDs

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

Extract text after last comma in string

Months from this month

Diff in minutes

Random 2 digit number from record ID