1

I have a dropdown menu on google sheets and want to record every time there is something chosen from the list or changed into a "recording" column.

Example,

Dropdown(Cell A2) options are red, green, blue, pink,

I want the column to record every time someone changes the option.

RESULTS (column B2) every next available cell

red

green

green

blue

green

blue

Here is the example in google sheets https://docs.google.com/spreadsheets/d/1qqxCBBg0UgNV6EItEb3FfzcfDdtQ-v2QEMjWfiWw/edit?usp=sharing

I was told I need use the app script with this =INDIRECT("A1") Open the script editor, and type this function.

` function archivesFun(){ const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName('archive');

const data = sheet

.getRange(1,2)

.getValue();

sheet.appendRow([data]); }`

I am just completely new to coding, any help is appreciated!

Echo18
  • 25
  • 5
  • I have to apologize for my poor English skill. Unfortunately, when I saw your provided Spreadsheet, I cannot find `a "recording" column`. And also, I cannot find `Dropdown(Cell A1) options are red, green, blue, green,`. And, in your showing script, `archive` sheet is used. But in your provided Spreadsheet, `archive` sheet is not included. So, I'm worried that in your question, you might have miscopied your showing script or your provided Spreadsheet. How about this? – Tanaike Jul 02 '22 at 05:44
  • I have corrected the link! – Echo18 Jul 02 '22 at 05:59
  • Do you mean like a log system which just records the changes on the dropdown? Also as Tanaike mentioned the sheets you've shared does not have a dropdown in A2. – Logan Jul 02 '22 at 06:07
  • Thank you for repling and adding more information. From your reply, I proposed an answer. Could you please confirm it? If I misunderstood your question and that was not useful, I apologize. – Tanaike Jul 02 '22 at 06:16
  • @Tanaike Thank you for your patience! Hopefully, you can now see the dropdown menu. I am new to programing and coding, thank you for your patience! – Echo18 Jul 02 '22 at 06:27
  • @DanF yes, like a system log where each input is recorded going down the column – Echo18 Jul 02 '22 at 06:28
  • Thank you for replying. I have to apologize for my poor English skill. Unfortunately, I cannot understand `Hopefully, you can now see the dropdown menu.`. Can I ask you about the detail of it? If I misunderstood your question, I have to apologize. – Tanaike Jul 02 '22 at 06:29
  • [Tables](https://webapps.stackexchange.com/a/161855/) are a better alternative than spreadsheets to show your data structure. Questions asked here should be self contained. If you share spreadsheets, make sure to also add images of your sheet. [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. – TheMaster Jul 02 '22 at 14:17
  • @Tanaike is there a way of achieving this in two sheets? let's say sheet1 has the two drop-down menus (Menu 1) (Menu 2) and sheet2 has the columns. Column A records Menu 1 and Column B records Menu 2? – Echo18 Jul 04 '22 at 05:05
  • @Tanaike i created a new post, hopefully, you can help!!! https://stackoverflow.com/questions/72863258/dropmenu-record-part-2 – Echo18 Jul 05 '22 at 01:49
  • Thank you for your response. I would like to check your new question. – Tanaike Jul 05 '22 at 03:00

1 Answers1

1

I believe your goal is as follows.

  • When the dropdown list of "A2" of "Sheet1" is changed, you want to copy the value to the last row of the column "B".

In this case, how about the following sample script?

Sample script 1:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. When you want to use this script, please change the value of a dropdown list of "A2" in "Sheet1". By this, the script is automatically run.

In this sample script, when the dropdown list of "A2" of "Sheet1" is changed, the value of the dropdown list to the last row of the column "B".

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "Sheet1" || range.getA1Notation() != "A2") return;
  sheet.getRange(sheet.getLastRow() + 1, 2).setValue(e.value);
}

Sample script 2:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. When you want to use this script, please change the value of a dropdown list of "A2" in "Sheet1". By this, the script is automatically run.

In this sample script, when the dropdown list of "A2" of "Sheet1" is changed, the value of "B2" is copied to the last row of the column "B".

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "Sheet1" || range.getA1Notation() != "A2") return;
  range.offset(0, 1).copyTo(sheet.getRange(sheet.getLastRow() + 1, 2), {contentsOnly: true});
}

Note:

  • If the last row of column "B" is different from other columns, I thought that the following sample script might be useful.

      function onEdit(e) {
    // Ref: https://stackoverflow.com/a/44563639/7108653
    Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
      const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
      return search ? search.getRow() : offsetRow;
    };

    const range = e.range;
    const sheet = range.getSheet();
    if (sheet.getSheetName() != "Sheet1" || range.getA1Notation() != "A2") return;
    sheet.getRange(sheet.get1stNonEmptyRowFromBottom(2) + 1, 2).setValue(e.value);
  }

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • You are Amazing!! How can I have it where Sheet 1 has two dropdown menus but sheet 2 Recordsin separate columns? – Echo18 Jul 03 '22 at 17:44