Google Sheets – Using Variables in getRange

google sheetsgoogle-apps-scriptgoogle-sheets-macro

I have a simple need to copy a range (E4:G4) and paste it X rows below, determined by the value of a cell, C2. I think I may be misunderstanding the offset function since when I run the routine, I get a "range not found" error, presumably because "dest" is not defined properly. Any suggestions?


function macrotest() {
  var spreadsheet = SpreadsheetApp.getActive();
  var rowoffset = spreadsheet.getRange("C2").getValue()
  var cell = spreadsheet.getRange("E4");
  var dest = cell.offset(rowoffset,1) 
  spreadsheet.getRange(dest).activate();
  spreadsheet.getRange('E4:G4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

Best Answer

To solve the error

change

spreadsheet.getRange(dest).activate();

dest.activate();

Related Solutions

Google Apps Script – Passing Variables to Deployed Web Application

You can modify the function params to accept the argument it's given, and use it in forming the string that it returns. Like so:

function params(row) {                                   // added row here
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssid = ss.getId();
  var sheet = ss.getActiveSheet();
  var sheetName = sheet.getName();
  return '?ssid='+ssid+'&name='+sheetName+'&row='+row;   // added row here
}

Now when you use ...exec" & params(1)) the value of 1 will be correctly appended to the URL used to invoke the web app: the URL will have &row=1 and consequently, the doGet function of the web app will receive this value as e.parameters.row.

However it's easier to just append 1 yourself: in the hyperlink function, put

...exec" & params() & "&row=1" )

...exec" & params() & "&row=" & row() )

There is no need to run a number through a custom function when you can just append it to the URL in the spreadsheet. The reason the custom function params is needed is to pass the values that spreadsheet functions cannot access: spreadsheet id and sheet name.

Google Sheets – Using Named Constants

Currently, this is not possible in Google Sheets, because Select data range field can intake only cell/array references. Therefore, best you can have is to create the second sheet where you will store your variables/constants and name them with Named ranges - and then right-click the sheet and select Hide sheet

Best Answer

Related Solutions

Google Apps Script – Passing Variables to Deployed Web Application

Google Sheets – Using Named Constants

Related Topic