Get dropdown options in google sheets in table

I have a table in Google Sheets where one of the columns is a dropdown menu.

In another sheet, I want to get all the individual options from this dropdown menu. I modified this solution by @Tanaike to work on just a single cell instead of an entire range.

/**
* @OnlyCurrentDoc
*
*/
function GET_DROPDOWN_OPTIONS(cell_reference_str) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet, cell_name;
  
  // Check if input contains '!', indicating a specific sheet is referenced
  if (cell_reference_str.indexOf('!') > 0) {
    const [sheetName, cell_name] = cell_reference_str.split('!');
    sheet = sheetName;
  } else {
    sheet = ss.getActiveSheet().getName(); // Get the active sheet's name
    cell_name = cell_reference_str;
  }

  // Now check if the sheet exists in the spreadsheet
  const actualSheet = ss.getSheetByName(sheet);
 
  if (!actualSheet) {
    throw new Error("Sheet not found: " + sheet);
  }

   var cell = actualSheet.getRange(cell_name)

  // Get data validation for the specified cell
  const dataValidation = cell.getDataValidation();
  
  if (dataValidation) {
    const criteriaType = dataValidation.getCriteriaType();
    const criteriaValues = dataValidation.getCriteriaValues()[0];
    
    if (criteriaType == SpreadsheetApp.DataValidationCriteria.VALUE_IN_LIST) {
      const result = criteriaValues.map(option => [option]);
      return result;
    } else if (criteriaType == SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) {
      const result = criteriaValues.getValues().flat().map(option => [option]);
      return result;
    }
  }

  // Return empty array if no data validation or unsupported validation type
  return [[""]];
}

This works fine when the input is a reference to a normal cell with a dropdown (i.e. not a cell in a table column) but this does not work when the referenced cell is a cell in a table column with a dropdown menu.

In the case where the cell is part of a table, the dataValidation is just none

How can I resolve this?

Solution

Modification points:

In the current stage, it seems that the data validations in a Table cannot be retrieved as the data validations. So, in your script, dataValidation is null. I thought that this might be the reason for your current issue. In this case, Spreadsheet service (SpreadsheetApp) and Sheets API return the same result. I expect that this will be resolved in future updates.

From the above issue, in this answer, I would like to propose a workaround. The flow of the workaround is as follows.

Create a temporal sheet.
Copy the cell with the data validation in a Table to the temporal sheet.
- By this, the copied data validation can be correctly used with your script.
Retrieve the values from the copied data validation.
Delete the temporal sheet.

When this flow is reflected in your script, it becomes as follows.

Modified script:

function GET_DROPDOWN_OPTIONS(cell_reference_str) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet, cell_name;

  // Check if input contains '!', indicating a specific sheet is referenced
  if (cell_reference_str.indexOf('!') > 0) {
    [sheetName, cell_name] = cell_reference_str.split('!');
    sheet = sheetName;
  } else {
    sheet = ss.getActiveSheet().getName(); // Get the active sheet's name
    cell_name = cell_reference_str;
  }

  // Now check if the sheet exists in the spreadsheet
  const actualSheet = ss.getSheetByName(sheet);

  if (!actualSheet) {
    throw new Error("Sheet not found: " + sheet);
  }

  var cell = actualSheet.getRange(cell_name)

  // Get data validation for the specified cell
  const dataValidation = cell.getDataValidation();

  // --- I modified the below script.
  const scriptInIfstatement_ = dataValidation => {
    const criteriaType = dataValidation.getCriteriaType();
    const criteriaValues = dataValidation.getCriteriaValues()[0];
    if (criteriaType == SpreadsheetApp.DataValidationCriteria.VALUE_IN_LIST) {
      const result = criteriaValues.map(option => [option]);
      return result;
    } else if (criteriaType == SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) {
      const result = criteriaValues.getValues().flat().map(option => [option]);
      return result;
    }
  }
  if (dataValidation) {
    return scriptInIfstatement_(dataValidation);
  } else {
    const temp = ss.insertSheet("temp" + Utilities.getUuid());
    const tempRange = temp.getRange("A1");
    cell.copyTo(tempRange);
    const tempDataValidation = tempRange.getDataValidation();
    const res = tempDataValidation ? scriptInIfstatement_(tempDataValidation) : [[""]];
    ss.deleteSheet(temp);
    return res;
  }
}

With this modification, when a cell with the data validation in a Table is used, the values in the data validation are returned using the above workaround.