Lists subtraction without losing duplicates with GAS

AppSheet Tips & Tricks
,
1

AppSheet’s native functionality results in losing duplicates in the resulting list list when a list subtraction is performed.

So LIST( “Apple” , “Apple” , “Banana” , “Cherry” , “Cherry” , “Cherry” ) - LIST(“Apple”, “Cherry”) results into LIST( “Banana”)

With the help of following tiny Google Apps script (GAS), we can perform list subtraction without losing duplicates. The GAS is executed using Appsheet automation event of form save. The return value from the GAS which is the list subtraction result of ([Large_List] - [Small_List]) is used to populate the results column called [Subtractions_Result] in the backend Google sheet of the lists table. The AppSheet automation’s return value option is used in a data action step to update this [Subtractions_Result] column.

The GAS created with the help of AI is as below

Summary 
function LIST_SUBTRACT(largeList, smallList) {
  // Convert inputs to arrays (handles both ranges and comma-separated strings)
  largeList = Array.isArray(largeList) ? largeList.flat() : largeList.split(",");
  smallList = Array.isArray(smallList) ? smallList.flat() : smallList.split(",");

  // Trim spaces
  largeList = largeList.map(x => x.toString().trim());
  smallList = smallList.map(x => x.toString().trim());

  // Create a frequency map for smallList
  const freq = {};
  smallList.forEach(item => {
    freq[item] = (freq[item] || 0) + 1;
  });

  // Build the result by subtracting counts
  const result = [];
  largeList.forEach(item => {
    if (freq[item] > 0) {
      freq[item]--;
    } else {
      result.push(item);
    }
  });

  return result;
}

In the app, it looks like below

The app subtracts [Small_List] from the [Large_List] and the resulting list is [Subtraction_Result] In the example screenshot below, the user adds Mary and Tim to the [Short_List] and as a result the number of elements named Mary reduce from 3 to 2 and Tim also from 3 to 2 but not 0 as it happens with AppSheet’s native list subtraction.

List_Subtraction_301225_Latest

2 Likes