Skip to content

Instantly share code, notes, and snippets.

@sco-tt
Last active March 30, 2021 10:40
Show Gist options
  • Save sco-tt/ebfd79bc95a6bbb6881c to your computer and use it in GitHub Desktop.
Save sco-tt/ebfd79bc95a6bbb6881c to your computer and use it in GitHub Desktop.
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.
/* 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;
}
}
@sai-creator
Copy link

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.

@sco-tt
Copy link
Author

sco-tt commented Aug 27, 2019

@sai-creator please write me an email.

@sachinbm579
Copy link

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);
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment