CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
chubinh
chubinh

Reputation: 25

Create a custom function to validate Data in Google Spreadsheet by App Script

I want to make the function like this in google spreadsheet:

For example, if In "Column 1" I choose value "1", in "Column 2" will show only values "11,12,13" .

1

Upvotes: 0

Views: 1164

Answers (1)

Martí
Martí

Reputation: 2861

This can be split into 2 independent problems:

  1. When setting a value on the E column, add set the proper validations for column F of that row.
  2. When setting a value on the F column, automatically set column G of that row.

This is actually quite advanced as it requires quite a bit of knowledge about Apps Script. I'll go step by step but not in code order but more in a "how I would think about it" order. I'm leaving a bunch of references to everything I'll use at the end. If you have any questions, please ask for clarification :)

Setting values on column G

I'll start with this one as it's slightly simpler than the other.

When editing an spreadsheet you can edit multiple cells in a single action. For example: pasting multiple cell values will only trigger a single onEdit but the range defined will be of the modification. Because of that, we can't assume that there is only a single cell and we have to treat it like it's always multiple cells. Because of that, the way of setting the values will be:

  1. Get the edited range in the F column (and only the F column)
  2. Get the values
  3. Map the values to the linked values
  4. Get the range to set the values to
  5. Set the values

Getting the edited range in column F

To start with, let's create a function for this. I'll call it setColGValues(e) (feel free to change it).

function setColGValues(e) {
  // Code goes here
}

e is the event for onEdit as it contains information that we'll use to extract the information about the edit.

Now we need to get the affected part of column F. To do so I'll be using a utility function. It will be explained later on. To get it we'll do:

  const range = rangeIntersection(e.range, 'F2:F')
  if (!range) return

This will get a rance with all the edited cells in the F column (starting with F2). If there is no such range (for example the edit is in another column), return.

Getting the values

Simply call Range.getValues(). I'll save it to values.

This is a 2 dimensional array. It's basically an array for rows, where each row is an array of values. So this table

A B C
1 2 3
4 5 6

Would have the following equivalent value:

[
  [1, 2, 3],
  [4, 5, 6],
]

Setting the values

When we need to set the values we'll map them using Array's map. Because we know that we'll only have a single value per row, we can iterate only the columns and get the only value of each row:

  values.map(([value]) => /* something */)

Because the values are always the same, the simplest solution is to create an object which is useful for the mapping. Together with that it's like so:

  const gValues = values.map(([value]) => [mapping[value]])

Make the value mapping

mapping is defined before this line and it's an object that maps both columns. In your example is defined by the range B3:D and its value would be the following:

{
  11: 111,
  12: 121,
  13: 131,
  21: 211,
  22: 221,
  23: 231,
  31: 311,
  32: 321,
  33: 331,
}

To extract the value of that range we first need to get the sheet it is on

  const sheet = e.source.getSheetByName('Your sheet name')

e.source is the spreadsheet that called the event.

Then get the values from the range.

  sheet.getRange(`B3:D`)
    .getValues()

But this values may not be valid. For example there could be a value on a D cell but not having a value on B. Because of that, we need to filter the values to make sure there is one. Note that having a value on the B column but not the D can make sense for your case. In this case we'll use the filter function. Again for ease of user I'll use the destructing syntax.

  /*[...]*/
    .filter(([b, _d]) => b)

At this point we have an array with pairs of values. We want the first to be the key and the second the value of a new object. Fortunately for us, Object.fromEntries does exactly that. So everything together:

  const mapping = Object.fromEntries(
    sheet.getRange(`B3:D`)
      .getValues()
      .filter(([b, _d]) => b !== '')
  )

Setting the values

The first thing we need is the equivalent range for column G for the range for column F that we have. The best way of doing that is creating another range offsetted by one. Range already has a function for this: offset(rowOffset, columnOffset). Then we can call setValues() with the mapped values:

  range.offset(0, 1).setValues(gValues)

Setting validations for column F

Setting validations of column F based on column E is pretty similar to the previous problem. The differences are:

  • The obvious change on columns
  • You call setDataValidations(rules) instead of setValues.
  • Working with rules instead of values.
  • The generation of the mapping between the E column and the validations.

The main difference is that now for each entry on column A we have multiple values on column B to be attached. The group then need to be converted into a rule and, finally, into an object to map with.

Creating rules

There are multiple ways of doing this. I'll leave with one but I'd encourage you to try more, as it's a good exercise to understand how things work.

  const entries = []
  let currentA
  let currentMap
  for (let [a, b] of sheet.getRange('A1:B').getValues()) {
    if (a !== currentA) {
      if (currentMap) {
        entries.push([
          currentA,
          SpreadsheetApp.newDataValidation().requireValueInList(currentMap, true).build()
        ])
      }
      currentA = a
      currentMap = []
    }
  }
  
  const mapping = Object.fromEntries(entries)

SpreadsheetApp.newDataValidation().requireValueInList(values, true).build() creates a data validation with a list of values and a dropdown. Then we can convert Entries to a mapping.

Joining them

You may join them by simply calling both individual functions:

function onEdit(e) {
  setColFValidation(e)
  setColGValues(e)
}

It actually doesn't matter the order so you could order them in the opposite order.

If you have multiple sheets, you need to check that the sheet is the correct one:

  if (e.range.getSheet().getName === 'Your sheet name') {
    // Call the functions
  }

Useful utilities used

function rangeIntersection(a, b) {
  // Allow the second argument to be an A1 notation range
  if (isString(b))
    b = a.getSheet().getRange(b)

  // Compute the bounds
  const top    = Math.max( a.getRow(),        b.getRow()        )
  const bottom = Math.min( a.getLastRow(),    b.getLastRow()    )
  
  const left   = Math.max( a.getColumn(),     b.getColumn()     )
  const right  = Math.min( a.getLastColumn(), b.getLastColumn() )

  // If the bounds don't make a range (ie. the ranges don't intersect)
  if (top >= bottom || left >= right)
    return null

  // Get the range
  return a.getSheet().getRange(top, left, bottom - top, right - left)
}

rangeIntersection returns the range that is part of both ranges. The second argument is allowed to be a string.

function isString(v) {
  return typeof v === 'string' || v instanceof String
}

isString returns if the value is a string. Useful because string can be of type string or be an object of class String.

References

Upvotes: 1

Related Questions