Need help recording from dropdown list in google sheets-CodePudding

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

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!

CodePudding user response：

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:

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:

Simple Triggers