Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsvlookupsetvalue

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

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 defined.

So, how to find the Today dated Row (or Range)?

Solution

  • 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