1

Sheet1

Sheet2

I had a previous post here and got many helpful replies.

There is a dropdown menu for each "Chair" and "Bed" in the first Sheet named "House". There is a dropdown menu for each Bed and Chair with options for "eat,sleep,chill." The goal is when someone changes the dropdown menu for Bed 1 to "rest", on sheet2, the column labeled Bed 1 will record the entry.

Hopefuly it looks something like this:

endresult

Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59
Echo18
  • 25
  • 5
  • In your image of https://i.stack.imgur.com/uNPSX.png , for example, the label of the dropdown list of "E4" is "Bed 1"? If my understanding is correct, where can I see "Chair"? And, what are the labels of "A", "B",,,? And also, I cannot understand `the column labeled Bed 1 will record the entry.`. In order to correctly understand your question, can you provide the sample input and output situations you expect as the images? – Tanaike Jul 05 '22 at 03:01
  • Can you share sample/copy of your spreadsheet we can work with, would save us some time replicating what you have in your screenshots. Remove sensitive data if any. – Logan Jul 05 '22 at 03:15
  • @Tanaike, apology. the "Chair" Is not labeled chair. It's been given a letter. A-K. input example for Bed 1 -Sleep -Relax -Sleep -Eat the idea is to track someones activaties in what order they do. – Echo18 Jul 05 '22 at 03:54
  • https://docs.google.com/spreadsheets/d/1dKx2lxX61objzZz_l6qH-ItNXpwx1AIp_PNDJ36yzro/template/preview @DanF – Echo18 Jul 05 '22 at 04:01
  • Thanks for providing the sheets. And thanks @Tanaike, however I do not mind if you jump in since this is an open community and any help is appreciated. Anyways, working around the sheets script now. Hopefully in the future questions you provide complete details and minimal reproducible examples so you are more likely to get help and reduce back and forth comments. https://stackoverflow.com/help/minimal-reproducible-example – Logan Jul 05 '22 at 04:49

1 Answers1

1

Try:

function onEdit(e) {
  const ss = e.source;
  const range = e.range;
  const row = range.getRow()
  const col = range.getColumn()
  const dataSheet = ss.getSheetByName('Data');
  
  var bedNumSource = ss.getActiveSheet().getRange(row + 1,col).getValue();
  var destCol = dataSheet.createTextFinder(bedNumSource).findNext().getColumn();

  if(ss.getActiveSheet().getName() == 'House') {
    let newColumnValues = dataSheet.getRange(2, destCol, dataSheet.getLastRow(), 1).getValues().flat().filter(String).concat(e.value);
    dataSheet.getRange(2, destCol, newColumnValues.length, 1).setValues(newColumnValues.map(e => [e]));
  }
}

Result:

enter image description here

This works for bed but for chair for it to work I suggest that you align it exactly the same as with Data Sheet not just the letters (Chair A, Chair B, Chair C, etc.) because the script checks the text of the cell below the edited cell then finds it on the Data Sheet. If you still want to keep it as just the letters let me know I can try to edit the script.

I have added the script to the sample docs link you've provided so you can test.

Logan
  • 1,691
  • 1
  • 4
  • 11
  • I will make the change of renaming the chairs from simple letters to “chair A-K” “A”->”chair A” that way, your suggestion makes it so there should be no complications for the script. :) – Echo18 Jul 05 '22 at 07:48