Google Sheets/AppScript - Copying Data from one Sheet to Another

Question

I want to create a button in my sheet that copies (or cuts and pastes) 'custom items' from one sheet (Daily Lab Orders) to the last row of the finished sheet (Finished 2016) once they're completed. I then want it to delete the row containing the 'custom item'. Logically, I thought I'd select the row, click the button, and the do this over and over for each 'custom item' that is finished.

The snag I've run into is that the script copies only cell A1. It doesn't copy any other values along the first row and it will not copy anything below row 1. A1 is frozen and it is using the function NOW() to constantly update the time.

Below is my script. Any assistance, please? Thank you.*

*The row deletion part of the script hasn't been written yet.

function myFunction() {

 var ss = SpreadsheetApp.openById('15yEY5ZX1HyOQFn-BkMXvksivADF3-8g9TnsIEI8aaL8'); //replace with source ID

 var source = ss.getSheetByName('Daily Lab Orders 2016'); //replace with source Sheet tab name
 var SRange = source.getActiveRange(); //assign the range you want to copy
 var SData = SRange.getValues();

 var target = ss.getSheetByName('Finished 2016'); //replace with destination Sheet tab name
 var last_row = target.getLastRow();
 var target_range = target.getRange("A"+(last_row+1)+":G"+(last_row+1))
 target.getRange(target.getLastRow()+1, 1, SData.length, SData[0].length).setValues(SData);


}

It's not clear how you are triggering myFunction. If you are triggering this from outside the spreadsheet, then getActiveRange() might not return the range you have selected in any given copy of the sheet. Can you clarify how you are calling myFunction() ? — Cameron Roberts

Harold Harold · Accepted Answer · 2016-09-14T07:37:29

when you are using :
var ss = SpreadsheetApp.openById('15yEY5ZX1HyOQFn-BkMXvksivADF3-8g9TnsIEI8aaL8');
You are open ing a spreadsheet, when you open a spreadsheet the activeRange is always A1.
So, your script is acting normally.
If the function is wrote in the spreadsheet and for example if you are triggering it from a menu button then you could change the openByIdwith getActive:

var ss = SpreadsheetApp.getActive();
var source = ss.getActiveSheet();
var SRange = source.getActiveRange();

Here activeRange reflect the user selection, if you prefer to retrieve all the sheet content then you should change for :

 var ss = SpreadsheetApp.openById('15yEY5ZX1HyOQFn-BkMXvksivADF3-8g9TnsIEI8aaL8'); //replace with source ID
 var source = ss.getSheetByName('Daily Lab Orders 2016'); //replace with source Sheet tab name
 var SRange = source.getDataRange();

getDataRange take all the sheet content.

Google Sheets/AppScript - Copying Data from one Sheet to Another

1 Answers