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