How to get row ( or Range ) by Date in Apps Script-CodePudding

I have a google spreadsheet. ( find here )

Date	Name	Number
01/01/2023	Ajay	123
02/01/2023	Vijay	158
03/01/2023	Rakesh	258
04/01/2023	Baby	745
05/01/2023
06/01/2023
07/01/2023

I want to set values in B & C columns where Date is today in Column A with the values of B2 & C2

I have tried this ..

function myFunction() {
  ss = SpreadsheetApp.getActiveSpreadsheet() ;
  values = ss.getRangeByName("Sheet1!b2:c2").getDisplayValues() ;
  
  ss.getRange(TodayDatedRow).setValues(values) ;
}

I know it will not work. Because TodayDatedRow is not defind.

so how to find the Today dated Row ( or Range ) ..?

Answer

function getTodayRow() {
  try {
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    values = ss.getRangeByName("Sheet1!B2:C2").getDisplayValues() ;
    let matches = ss.getRangeByName("Sheet1!A5:A").getValues();
    let today = new Date();
    // remove time
    today = new Date(today.getFullYear(),today.getMonth(),today.getDate());
    // get the row index
    let index = 5   matches.findIndex( row => row[0].valueOf() == today.valueOf() );
    console.log("row index = " index);

    ss.getSheetByName("Sheet1").getRange(index, 2, 1, 2).setValues(values) ;
  }

  catch(err) {
    console.log(err)
  }
}

CodePudding user response：

Here is an example of how to get the row number for the the row containing today's date.

function getTodayRow() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Sheet1");
    let values = sheet.getDataRange().getValues();
    let today = new Date();
    // remove time
    today = new Date(today.getFullYear(),today.getMonth(),today.getDate());
    // get the row index
    let index = values.findIndex( row => row[0].valueOf() == today.valueOf() );
    console.log("row index = " index);
  } 
  catch(err) {
    console.log(err)
  }
}

Execution log

5:55:19 AM  Notice  Execution started
5:55:20 AM  Info    row index = 5
5:55:21 AM  Notice  Execution completed

Which means row 6.

References