How can I limit the Range of onSelectionChange and set the values from selected row to other cells?-CodePudding

I am trying to figure out how to limit the range when using onSelectionChange. I have had some limited success with a few other onSelectionChange answers here but can't quite get it to do what I need..

Here is my Sample Sheet outlining what I am trying to accomplish...

GOAL: I would like the onSelectionChange to get the values in Col A:F in the selected row, IF the selected cell is in the named range of TESTsheet!A10:F (selecting any cell in rows 1-9 would not return any values)

The values from the selected row would be set in spisific cells.

For example: If any cell in row 24 Col A-F was selected, the values from A24, B24, C24, D24, E24, F24 would be set in B2, B4, B6, E2, E4, E6 (or other named cells) respectively.

I have gotten close with these answers:

CodePudding user response：

Working but the IF is slow...

This seems to work but is there some way to speed up the IF statement? Google seems to handle each getRange line about one second after the other.

Is there some way to combine these into one line so all setValues happen at the same time? If I need to get 30 or 40 values from a row this could be too slow to be useful..

function onSelectionChange(e) {
  const as = e.source.getActiveSheet();
  const row = e.range.getRow();
  const col = e.range.getColumn();
  
  // IF statement works only on TESTsheet!A10:F100 and can be expanded by changing the row and col numbers
  
  if( as.getName() == 'TESTsheet' && row>9 && row<100 && col<7 ){
      
      // as.getRange('TARGET CELL').... .getRange(row is selected row, starting column, how many rows to get, number of columns to get)
      
      as.getRange('B2').setValue(e.source.getSheetByName('TESTsheet').getRange(row, 1, 1, 1).getValue());
      as.getRange('B4').setValue(e.source.getSheetByName('TESTsheet').getRange(row, 2, 1, 1).getValue());
      as.getRange('B6').setValue(e.source.getSheetByName('TESTsheet').getRange(row, 3, 1, 1).getValue());
      as.getRange('E2').setValue(e.source.getSheetByName('TESTsheet').getRange(row, 4, 1, 1).getValue());
      as.getRange('E4').setValue(e.source.getSheetByName('TESTsheet').getRange(row, 5, 1, 1).getValue());
      as.getRange('E6').setValue(e.source.getSheetByName('TESTsheet').getRange(row, 6, 1, 1).getValue());
  }
  
  
  // the else is optional will put the text "test" in TESTsheet!A1 if a cell is selected outside of the range TESTsheet!A10:F100
  
  //else{    
  //    as.getRange('A1').setValue('test');
  //}
}

CodePudding user response：

This limits the actions to columns A-F of you sheet name

function onSelectionChange(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == 'Sheet0' && e.range.rowStart > 9 && e.range.rowStart < 100 && e.range.columnStart < 7) {
    const vs = ['B2','B4','B6','E2','E4','E6'].map(e => sh.getRange(e).getValue());
    sh.getRange(e.range.rowStart,1,[vs].length,vs.length).setValues([vs]);
  }
}