Google-sheets – Pass cell value into Google script

google sheetsgoogle-apps-script

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();
}

Best Answer

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
Related Topic