Get current cell and copy it to other sheet google sheets

Question

I want to copy 3 values from sheet1 to sheet2 using custom button on clicking on it.

I have on sheet1

  A        B        C      D      F       G
1 bbb      kkkk     12     2      3       my custom button here
2 hhhh     tttt     13     6      8       my custom button here
3 ttt      qqqq     16     1      4       my custom button here

And my sheet2

   A        B          C
1

What I want is when I click on my custom button I want to copy values FROM the clicked buttons row ex B1 C1 F1 in sheet1 to A1 B1 C1.

I tried this

function CopyPaste() {
   var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1');            
   var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet2');   
   sheet1.getRange('G2').getValue().copyTo(sheet2.getRange('A45')) ;
   sheet1.getRange('I2').getValue().copyTo(sheet2.getRange('B45')) ;
   sheet1.getRange('K2').getValue().copyTo(sheet2.getRange('C45')) ;
}

BUT this getting always G2 I2 K2 and not the clicked row . AND from this I'm not getting the calculated value , I'm getting instead the formula script which I don't want. I want get just calculated values from sheet1 . they are calculated.

Rubén Rubén · Accepted Answer · 2016-09-12T14:02:43

Assuming that by "custom buttons" the question is referring to images with assigned scripts, they don't have an automatically assigned event object like occurs with triggers like On Open among others.

One solution is to create a parameterized main function and a function for each set to parameters to be triggered by the "custom buttons". If you have a button for each row, and require to send the row number as parameter, you will need a function for each row.

Example

function customButton_row1() {
   CopyPaste(1);
}

function customButton_row2() {
   CopyPaste(2);
}

function CopyPaste(row) {
   var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1');            
   var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet2');   
   sheet1.getRange('G' + row).getValue().copyTo(sheet2.getRange('A45')) ;
   sheet1.getRange('I' + row).getValue().copyTo(sheet2.getRange('B45')) ;
   sheet1.getRange('K' + row).getValue().copyTo(sheet2.getRange('C45')) ;
}

Get current cell and copy it to other sheet google sheets

1 Answers