1

In my Sheets projects I often have to write values at the end of the sheet programmatically. I use data validations, some of which don't accept invalid values. My problem is that if a write operation fails, I end up with an incomplete input. I want my operations to either succeed 100% or not at all.

Suppose I have a single-column sheet that only accepts A, B or C values:

enter image description here

First I thought I could validate the input values before writing:

const sheet = ss.getActiveSheet(); // Single-column sheet
const dataRange = sheet.getDataRange(); // Single-column range that only accepts A, B or C values.
const values = [["A"], ["B"], ["C"], ["D"]]; // Invalid mock input. D is not valid.
const range = sheet.getRange(dataRange.getNumRows() + 1, 1, values.length, values[0].length);

range.getDataValidations().forEach((row, i) => row.forEach((rule, j) => {

  if (args[0] && values[i][j] && !args[0].includes(values[i][j])) {
      sheet.deleteRows(firstRow, values.length);
      throw new Error(`"${values[i][j]}" is invalid.`);
  }
}));

range.setValues(values); // Will fail because D is not a valid input value.

However this doesn't work, because for some reason writeRange doesn't have any validations. It seems like they get applied only after the range initialization, but before writing.

Then I thought I could just sacrifice some effectiveness with a try/catch block that deletes the newly created rows afterwards if the write operation fails.

const sheet = ss.getActiveSheet(); // Single-column sheet
const dataRange = sheet.getDataRange(); // Single-column range that only accepts "A", "B" or "C" values.
const values = [["A"], ["B"], ["C"], ["D"]]; // Invalid mock input. "D" is not valid.
const range = sheet.getRange(dataRange.getNumRows() + 1, 1, values.length, values[0].length);

try {
  range.setValues(values);
} catch(error) {
  sheet.deleteRows(range.getRow(), values.length); // Should remove the incompletely written range.
  throw error;
}

But it turns out that setValues throws an error that is not catchable! This issue has been submitted 7 years ago and still persists.

Any ideas for a workaround?

2 Answers 2

2

Solution: Simply add rows before initializing the range. This way I can access the validation rules.

const sheet = ss.getActiveSheet();
const dataRange = sheet.getDataRange();
const firstRow = dataRange.getNumRows() + 1;
sheet.insertRowsAfter(dataRange.getNumRows(), values.length);
const range = sheet.getRange(firstRow, 1, values.length, values[0].length);

range.getDataValidations().forEach((, i) => row.forEach((rule, j) => {

  if (rule && !rule.getAllowInvalid()) {
    const args = rule.getCriteriaValues();
    if (args[0] && values[i][j] && !args[0].includes(values[i][j])) {
      sheet.deleteRows(firstRow, values.length);
      throw new Error(`"${values[i][j]}" is invalid.`);
    }
  }
}));

range.setValues(values);
0

There are several workarounds, depending on your spreadsheet in-cell data validation rules you might have to use more than one. All of these options might do the validation before the row insertion.

One option is to replicate the in-cell data validation rules as JavaScript code.

Another option is to read the in-cell data validation rule, then transform it into JavaScript code.

A third option is be to use the Advanced Sheets Service instead of the Spreadsheet Service (Class SpreadsheetApp) as this service returns a response that could be handled by using JavaScript.

A fourth option is to use the Sheets API by using UrlFetchApp instead of the Spreadsheet Service as this API returns a response that could be handle by JavaScript.

Related

4
  • What exactly do you mean by "validation as JavaScript code"?
    – Julius
    Aug 21, 2022 at 17:08
  • On your first code, instead of validation && !validation.getAllowInvalid() use !['A','B','C'].includes(writeValues[i][j]) Aug 21, 2022 at 17:17
  • But that would require me to access the data validation rules.
    – Julius
    Aug 22, 2022 at 11:33
  • Regarding the Sheets API solution: append request doesn't throw if invalid input is written. batchUpdate doesn't add rows. I can't see how to make use of these solutions in my case.
    – Julius
    Aug 22, 2022 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.