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
- Adding a value to the Lists tab adds it to the dropdown; nothing has to change in the code.
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 projectMore 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.