CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
krug
krug

Reputation: 49

Google Sheets Placeholder Text

Anyone know how to add placeholder text into Google Sheets cells? Just want something to give an example which then disappears when the user inputs another value. Haven't found anything after an hour or so of searching :/

I've tried a couple things, all of which didn't work, they were mostly just using conditional formatting though.

Upvotes: 4

Views: 17570

Answers (1)

Emel
Emel

Reputation: 2462

What about using onSelectionChange(e) simple trigger? This triggers runs automatically when a user changes the selection in a spreadsheet

In the following example the logic is as follows:

  1. If the user clicks in the observed cell, in the example is A1, and the content is equal to the placeholder value, the cell goes empty.

  2. If the user clicks outside of the observed cell, and the content of the observed cell is empty, the cell is filled with the placeholder value.

function onSelectionChange(e) {
  const observedCell = 'A1'
  const observedRange = SpreadsheetApp.getActiveSheet().getRange(observedCell)
  const clickedRange = e.range.getA1Notation()
  const placeholder = "This is my placeholder"
  if (clickedRange === observedCell && observedRange.getValue() === placeholder) {
    observedRange.setValue("")
  }
  if (clickedRange !== observedCell && observedRange.getValue() === "") {
    observedRange.setValue(placeholder)
  }
}

Updated to have more than one placeholder in different cells.

function onSelectionChange(e) {
  const phData = [
    {c: "A1", ph: "Placeholder for A1"},
    {c: "C3", ph: "Placholder for C3"}
  ]
  phData.forEach((placeholders) => {
    const observedRange = SpreadsheetApp.getActiveSheet().getRange(placeholders.c)
    const clickedRange = e.range.getA1Notation()
    if (clickedRange === placeholders.c && observedRange.getValue() === placeholders.ph) {
      observedRange.setValue("")
    }
    if (clickedRange !== placeholders.c && observedRange.getValue() === "") {
      observedRange.setValue(placeholders.ph)
    }
  })
}

Upvotes: 3

Related Questions