Search - Appscript - targetRange column start is not at the column I ask for-CodePudding

I am still learning a lot about Apps Script and needed to make a sheet for my work. At the moment I am busy on a Search function. The only problem is that the results that I want to get, do not go to that column. Only to a column way further.

function Search() {
  var sourcesheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Source");
  //USER INPUT
  sourceRowStart = 2; //Row to start getting formulas from
  sourceColumnStart = 13; //Column to start getting formulas from
  numberOfSourceColumnsToGet = 9; //Number of columns to get formulas from    
  targetRowStart = 4; //Row to start copying formulas to
  targetRange = 1; //Column to start copying formulas to
  //END OF USER INPUT
  activeSheet = SpreadsheetApp.getActiveSheet();
  sourceRange = activeSheet.getRange(sourceRowStart, sourceColumnStart, activeSheet.getLastRow(), numberOfSourceColumnsToGet);
  sourceFormulas = sourceRange.getFormulas();//Get only formulas from the source rang
  targetRange = activeSheet.getRange(targetRowStart, targetRange, sourceFormulas.length, sourceFormulas[0].length);
  targetRange.setFormulas(sourceFormulas);//Copy the formulas to the target range
}

Following is the link of a test-sheet for this question:

https://docs.google.com/spreadsheets/d/1b5Xa1z6xdeqyFbAVtxwc-U76iDteC1Sn46Ll_v9WGPU/edit#gid=713972882

Thankyou in advance to whoever could help me out!

CodePudding user response：

Try this:

function Search() {
  const ssr = 2; 
  const ssc = 13; 
  const sncols = 9;  
  const tsr = 4; 
  const tsc = 1;
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();  
  const sfA = sh.getRange(ssr, ssc, sh.getLastRow() - ssr   1, sncols).getFormulas();
  sh.getRange(tsr, tsc, sfA.length, sfA[0].length).setFormulas(sfA);
}

CodePudding user response：

Not sure if it causes the error in this case, but anyway. You have the line:

targetRange = 1; //Column to start copying formulas to

And then:

targetRange = activeSheet.getRange(targetRowStart, targetRange, sourceFormulas.length, sourceFormulas[0].length);

You're using the same variable targetRange for different purposes. It's not a good practice. It can get you glitches sometimes.

And to be honest, from yours spreadsheet and the code I failed to understand how the desired result should look like. Is the activeSheet suppose to be 'Database' sheet or 'Source' sheet, or something else? Etc.