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 */

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

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

  • How are you trying to pass the value B1 into the script? How is the script being called? Please add a simplified version of your script (a minimal, complete and verifiable example)
    – Rubén
    Jan 27, 2019 at 17:14

1 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
  • Thanks Ruben, I tried your suggestion, 'B' and "B", but it didn't work. rowNum is string type and return "#N/A". But in fact, the value of B1 in input_sheet, it returns a value (number). Jan 29, 2019 at 14:35
  • @JosephKong I'm sorry, but I don't understand what you mean (I didn't suggested 'B' / "B", they aren't valid cell/range references)
    – Rubén
    Jan 29, 2019 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.