Pass cell value into Google script

Question

I'm facing a problem, hope anyone can help me.

I have 3 sheets with different functions, data_sheet contains a thousand data with date in descending order in Col A, input_sheet is only for input data and target_sheet is to store the data after some application.

I'm writing a script. When I input a date in input_sheet, where this date matches with the same date in Col A of data_sheet, then the function will copy all the data from the top to that row that matched with the input date to target_sheet.

In data_sheet, A1 is the input date, and B1 I put the following formula, =match(value(A1),data_sheet!A:A,0), which is for looking the row number that input date A1 matched, say row_number is 20. Then I try to pass B1 (20) into the script so that I can copy A1:D20 to target_sheet.

However, I can't pass the value of B1 into script, it returns #N/A.

Anybody can help?

---- update ---- my code is below:-

function copy_Google_Historical_Data() {
  var this_sheet = SpreadsheetApp.getActiveSpreadsheet(); // for current workbook
  var source_sheet = this_sheet.getSheetByName("Data_fr_Google"); 
  var chart_data_sheet = this_sheet.getSheetByName("Chart_Data");
  var input_sheet = this_sheet.getSheetByName("Input");

  var row = input_sheet.getActiveCell();
  var range = input_sheet.getRange(2,8);
  var last_row = range.getDisplayValue();


  /* Copy data from Data_fr_Google to chart_data sheet */
  chart_data_sheet.getRange("A1:CC").clearContent();

  source_sheet.getRange("B2:CC" + last_row).copyTo(chart_data_sheet.getRange("A1:CB" + last_row),{contentsOnly:true})


  SpreadsheetApp.flush();
  Utilities.sleep(10000);  // wait for 10 seconds 

  copy_Data();
}

Answer 1

Maybe the problem is that the code doesn't match the description, as it's not getting the value of B1.

To get the value of B1 use something like the following:

var sheet = Spreadsheet.getActiveSheet();
var rowNum = sheet.getRange('B1').getValue(); // This assigns the value of B1 to rowNum