Is it possible to use a cell reference as row/column positions in getRange()?

Question

I am trying to make a button in a spreadsheet inventory that would rapidly add the right item in the right column of my inventory. I have a sheet dedicated to this button where you select the product to add to the inventory, then click GO and your item goes into one of the 10 columns of my inventory (depending on which item it is) along with quantity and a tag number. The selections in the list are associated with their number of items per pallet for a full pallet and the row number in which they should go in the inventory on a table on another sheet. I use the filter function to display both these informations on the button sheet so that when you press the button the displayed information is copied to the inventory using this line of script:

 var destSheet = ss.getSheetByName("Fiche");
 var lastRow = destSheet.getLastRow();
 var source = ss.getRange ('A3');
var destColumn = ss.getRange ('B15');

source.copyTo(destSheet.getRange(lastRow + 1, destColumn), {contentsOnly: true}); 

Where A3 is my amount of product on a full pallet and B15 is the column in which it should be copied. It seems that using variable lastRow is okay for giving getRange its row position but variable destColumn doesn't work for column position. It only works when I replace it with a constant number. I guess the type of data returned from getLastRow and getRange are not the same? any advice?

Answer 1

You are very close to the solution. You only need to add .getValues() at the end of the var destColumn = ss.getRange ('B15'); so it reads the number inside the cell (because the method .getRange() used later works with integers as detailed here). The final result should look like this:

function myFunction() {
  /*var ss = SpreadsheetApp.openById(
    "{SPREADSHEET ID}");*/
  var destSheet = ss.getSheetByName("Fiche");
  var lastRow = destSheet.getLastRow();
  var source = ss.getRange('A3');
  var destColumn = ss.getRange('B15').getValues();

  source.copyTo(destSheet.getRange(lastRow + 1, destColumn), {
    contentsOnly: true
  });
}