This google apps script copies values from a specific sheet within a Google Apps spreadsheet to a master spreadsheet. All values are added a a master acrhive where the name of the source sheet is automatically added as the first column. Additionally, all copy operations are logged in a separate sheet.

GA-copy-to-archive.js

/* Menu set-up. From https://developers.google.com/apps-script/guides/menus#menus_for_add-ons_in_google_docs_or_sheets */
function onOpen(e) {
  var menu = SpreadsheetApp.getUi().createAddonMenu(); // Or DocumentApp.
  if (e && e.authMode == ScriptApp.AuthMode.NONE) {
    // Add a normal menu item (works in all authorization modes).
    menu.addItem('Copy Values to Master', 'copySheetValues');
  } else {
    // Add a menu item based on properties (doesn't work in AuthMode.NONE).
    var properties = PropertiesService.getDocumentProperties();
    var workflowStarted = properties.getProperty('workflowStarted');
    if (workflowStarted) {
      menu.addItem('Copy Values to Master', 'copySheetValues');
    } else {
      menu.addItem('Copy Values to Master', 'copySheetValues');
    }
   menu.addToUi();
  }
}
// This is based on: http://igoogledrive.blogspot.com/2013/06/google-spreadsheet-how-to-copy-values.html
function copySheetValues() {
  
  // sourceSheetSheet Sheet - active sheet where our menu button is
  var spreadSheetName = SpreadsheetApp.getActiveSpreadsheet().getName();
  var sourceSheet = SpreadsheetApp.getActiveSheet();

  // Replaces XXXXXXXXXXX to manually plug in the ID of your destination sheet.
  // sheetDestination's subsheet defined by .getSheetByName; setting up a range our compiled archive
  // The master spreadsheet has two sheets (aka tabs) called "Copy Log" and "Compiled Archive"
  var destination = SpreadsheetApp.openById('XXXXXXXXXXXX');
  var compiledArchiveLastRow = destination.getSheetByName("Compiled Archive").getLastRow();
  destination.getSheetByName("Compiled Archive").insertRowAfter(compiledArchiveLastRow);
  var compiledArchiveCopyRange = destination.getSheetByName("Compiled Archive").getRange("A"+(compiledArchiveLastRow+1)+":D"+(compiledArchiveLastRow+1));
  
  //Set up a range our Log sheet 
  destination.getSheetByName("Copy Log").insertRowAfter(destination.getSheetByName("Copy Log").getLastRow());
  var copyLogSheetCopyRange = destination.getSheetByName("Copy Log").getRange("A"+(destination.getSheetByName("Copy Log").getLastRow()+1));

  //First do a temp copy to a temporary sheet that we'll give the name of 
  var tempSheet = destination.insertSheet(spreadSheetName + " [" + sourceSheet.getSheetName() + " ]", 0);
  // bit messy to read: offset() is measuring the range based on the sourcesheet # of columns and rows, setValues() is copying them in.
  tempSheet.getDataRange().offset(0, 0, sourceSheet.getDataRange().getNumRows(), sourceSheet.getDataRange().getNumColumns()).setValues(sourceSheet.getDataRange().getValues());
 
  //Add a column Copy the Site Name to the new column
  tempSheet.insertColumnBefore(1);
  var site = tempSheet.getRange("A1");
  site.setValue(spreadSheetName);
  site.copyValuesToRange(tempSheet, 1, 1, 2, tempSheet.getDataRange().getNumRows());

  //Copy the temp column to the master
  var tempSheetCopyRange = tempSheet.getRange(2, 1, sourceSheet.getLastRow() - 1, (sourceSheet.getLastColumn()+1));
  tempSheetCopyRange.copyTo(compiledArchiveCopyRange);
  
  //Create a log message and copy it to the log sheet
  //Row where the new data began
  compiledArchiveLastRow += 1; 
  //Row where the new data ended
  var compiledArchiveLastRowAfterCopy = destination.getSheetByName("Compiled Archive").getLastRow();
  var logMessage = '[' + spreadSheetName + ' | ' + sourceSheet.getSheetName() + ']  was copied on ' + new Date() + '. Copied data begins on row ' + compiledArchiveLastRow + ' and ends on row ' + compiledArchiveLastRowAfterCopy;
  copyLogSheetCopyRange.setValue(logMessage);
  
  //Prep values for alert and trigger alert function
  var alertMessage = logMessage;
  showAlert(alertMessage); 
  
   //Delete the Temp Sheet  
  destination.setActiveSheet(tempSheet);
  destination.deleteActiveSheet();

}   //End copySheetValues()


function showAlert(alertMessage) {
  var app = UiApp.createApplication();
  var ui = SpreadsheetApp.getUi(); 
  var result = ui.alert(alertMessage + '\n\n Hit OK to save this message to the log', ui.ButtonSet.OK); 
  // This might be unnecessary; trying to debug an issue with Dialog OK button blocks part of the script:
  // http://stackoverflow.com/questions/25121697/ui-alert-in-google-apps-script-blocking-copy-function
   if (result == ui.Button.OK) {
     return app;
 } else {
     return app;
 }
}

Thanks sco-tt. This is really useful for me.

Can you please help me further with my project?

I have three sheets. Tech Sheet, Admin Sheet and Manager Sheet. Tech Sheet data should be sent to Manager Sheet when they click on menu item similar to "Copy Values to Master". Can you please let me know how to publish above code as Google Add-On.

Tech Sheet doesn't have access to Manager Sheet. All the code is placed in Admin Sheet. So here I have to call code in Admin Sheet from Tech Sheet.

@sai-creator please write me an email.

Hi Sco-tt

Below is GAS Script i need fetch specific column from my below range

Can you please help or modify my query which has to be filtered irrelevant columns

function CopyRange() {
var sss = SpreadsheetApp.openById('1XVQzfiGdfWcZl4lOmKBa12asiFSnCLWIV2Yd0UNzrH4'); //replace with source ID
var ss = sss.getSheetByName('Testimport'); //replace with source Sheet tab name
var range = ss.getRange('A2:D'); //assign the range you want to copy
var rawData = range.getValues();

var data = []
data.push(rawData[0])

for (var i = 0; i< rawData.length ; i++){
if(rawData[i][2] == "Temp out") // Check to see if column K says ipad if not skip it
{
data.push(rawData[i])
}
}
var tss = SpreadsheetApp.openById('10kuspe4XXnguVJWDd2OdjScpUBp4yY07YpWfQj4VYxE'); //replace with destination ID
var ts = tss.getSheetByName('Sheet2'); //replace with destination Sheet tab name
ts.getRange(1,1,data.length, data[0].length).setValues(data);
}