Google sheets- script to copy and paste cell contents to cell using VLOOKUP type of action

Question

I would like to create a script that I can assign to a button in Google Sheets that will copy and paste cell contents in a given column to a cell in that same column that corresponds with a given date (designated in A1).

In the example below, I want to copy "hello" from B2, to the row that corresponds with 4/4/22 (the value in A1), so I would like to copy "hello" from B2 to B23.

I was able to get code that would copy to B23 specifically, but I need the destination to be dynamic (changing based on the date in A1)-that's where I'm hoping to use some sort of code that would mimic VLOOKUP. Any help would be appreciated!

Here's the sample spreadsheet: https://docs.google.com/spreadsheets/d/1xG7Tqp2F9EC6VSDDIIzbaXvlkY3toA-ZR3QIWHAvVPA/edit#gid=0

    function CopyTo() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('B23').activate();
spreadsheet.getRange('B2').copyTo(spreadsheet.getActiveRange(), 
SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

Answer 1

Here is the script:

function copy_from_b2() {
  copy_to_row('b')
}

function copy_from_c2() {
  copy_to_row('c')
}

function copy_to_row(column) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var date = sheet.getRange('a1').getDisplayValue();
  var dates = sheet.getRange('a3:a').getDisplayValues().flat().filter(String);
  var row = dates.indexOf(date) + 3;
  if (row < 3) return;
  var value = sheet.getRange(column + 2).getValue();
  sheet.getRange(column + row).setValue(value);
}

You can assign function copy_from_b2() to the blue button and copy_from_c2() to the red button.

Answer 2

It can be the formula:

=if(eq($A3,$A$1),B$2,"")

Put the formula in cells of the range B3:C39

Or you can use the array formula:

=ARRAYFORMULA(if(eq($A3:A,$A$1),B$2:C$2,""))

It's need to put in the cells B3 and clean all the cells in the range B4:C.

Answer 3

So I've taken the code from @YuriKhristich and tried to add comments explaining how it works, but there are some parts that I don't understand. Yuri, if you are able to clarify or if anyone else could, that would be great.

//this function essentially acts as a variable for the copy_to_row(column) function below
function copy_from_b2() {
  copy_to_row('b')
}
//this function essentially acts as a variable for the copy_to_row(column) function below
function copy_from_c2() {
  copy_to_row('c')
}

function copy_to_row(column) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var date = sheet.getRange('a1').getDisplayValue();//grabs the date in A1 that will be searched for in column A
  var dates = sheet.getRange('a3:a').getDisplayValues().flat().filter(String);//Gets the range of all the dates from A3 to bottom of column.  I don't understand what this portion of the code does, however: getDisplayValues().flat().filter(String)
  var row = dates.indexOf(date) + 3;//find the row that contains the match for the date (in A1) within the dates range.  Three is added to dates.indexOf(date) because the search starts in 4th row.
  if (row < 3) return;//This line has me completely confused.  I thought return was a keyword to tell Apps Script that you're going to return a value, but I'm not sure why it's used with if(row<3) since I only want to return values in rows>3
  var value = sheet.getRange(column + 2).getValue();//selects the value in either B2 or C2 (depending on whether copy_from_b2() or copy_from_c2() is being used )
  sheet.getRange(column + row).setValue(value);//copies value to the target column/row
}