GAS AI BuilderApps Script Assistant Sign in

A dropdown list from another sheet

Apps Script recipes › Sheets as a database

Keeps the choices in their own tab (Lists) and puts a dropdown on a column that follows that list.

File Code.gs

/** Puts a dropdown on column `column` of `sheetName` with the values of column A of the Lists tab. */
function addDropdown_(sheetName, column) {
  const ss = SpreadsheetApp.openById(SHEET_ID);
  const lists = ss.getSheetByName('Lists') || ss.insertSheet('Lists');
  const sheet = ss.getSheetByName(sheetName);
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInRange(lists.getRange('A2:A'), true)
    .setAllowInvalid(false)
    .build();
  sheet.getRange(2, column, sheet.getMaxRows() - 1).setDataValidation(rule);
}

/** The same choices for a <select> on the page. */
function getChoices() {
  const lists = SpreadsheetApp.openById(SHEET_ID).getSheetByName('Lists');
  return lists.getRange('A2:A').getValues().flat().filter(String);
}

Good to know

Let the AI put it in your appGAS AI Builder knows every recipe here. Describe your app and it uses this code where it fits, in a Google Apps Script project in your own Drive.

Use it in a project

More in Sheets as a database

Add a row safely when many people save at onceLockService keeps two saves from writing the same row; the row gets an Id and the time it was made.Write many rows at onceBuild a 2-D array and write it with one setValues call instead of one call per cell.Make a sheet with its header oncesetup() creates the tabs the app needs with a bold, frozen header, and adds missing columns to tabs that exist.Read a sheet as a list of objectsOne getValues() call, the first row as field names, dates turned into text so google.script.run can return them.Update or delete a row by its IdFinds the row by the Id column with TextFinder and writes the changed fields in one setValues call.