New to Java scripting so looking for some help, I am designing a Holiday spreadsheet with two sheets
- a master sheet that contains each department’s team member’s (Sheet 4)
- a Holiday sheet so users can input their teams holidays (Sheet5).
I have found a script that will insert a row or delete a row in Sheet 4 and Sheet 5, but I now need a script that will copy the contents of a newly inserted Row in sheet 4 and paste the data over to newly inserted row in sheet 5 in the same row as sheet 4.
For example if I add a row say in A10 in sheet 4 and put a Capital “Y” in A10 I would like the data from B10 to I10 to copy over to sheet 5 in row A10 to NJ10. I tried to find a solution to this but I can’t get it to work using the Copyto command.
Does anyone have a solution for this?
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet5 = ss.getSheetByName('Sheet5');
var sheet4 = ss.getSheetByName("Sheet4");
var values1 = sheet4.getRange("A:A").getValues();
var values2 = sheet4.getRange("A:A").getValues();
var values3 = sheet4.getRange("A:A").getValues();
// This starts off the remove and Insert Row Code
var deleteRow = new Array(); // Part of remove and Insert Row Code
var insertRow = new Array(); // Part of remove and Insert Row Code
var copyPaste = new Array(); // Copies to Sheet5
for (var i = 0; i < values1.length; i++) {
if (values1[i] == 'D') {
deleteRow.push(i + 1); // This line was added for debugging purposes.
var X = sheet4.getRange(i + 1, "2").getValues();
var response = Browser.msgBox('WARNING!! You are about to Delete the record for', X, Browser.Buttons.YES_NO);
Logger.log(response);
if (response == "yes") {
} else {
sheet4.getRange('A:A').clearContent();
Browser.msgBox('Cancelled', 'The operation has been cancelled', Browser.Buttons.OK);
}
}
}
for (var i = deleteRow.length - 1; i >= 0; i--) {
sheet4.deleteRow(deleteRow[i]);
}
for (var i = deleteRow.length - 1; i >= 0; i--) {
sheet5.deleteRow(deleteRow[i]);
}
for (var k = 0; k < values2.length; k++) {
if (values2[k] == 'I') {
insertRow.push(k + 1); // This line was added for debugging purposes.
sheet4.getRange('A:A').clearContent(); //Removes "I" to stop code repeating
}
}
for (var k = insertRow.length - 1; k >= 0; k--) {
sheet4.insertRowAfter(insertRow[k]);
}
for (var k = insertRow.length - 1; k >= 0; k--) {
sheet5.insertRowAfter(insertRow[k]);
}
for (var m = 0; m < values3.length; m++) {
if (values3[m] == 'Y') {
copyPaste.push(m + 1); // This line was added for debugging purposes.
sheet4.getRange('A:A').clearContent(); //Removes "Y" to stop code repeating
}
}
for (var m = copyPaste.length - 1; m >= 0; m--) {
sheet4.copyTo('B2:I13')(copyPaste[m]);
}
for (var m = copyPaste.length - 1; m >= 0; m--) {
sheet4.copyTo('B2:J13')(copyPaste[m]);
}
}
//----------------------------------------------------------------------------
function onEdit(e) {
// so 56930421_04
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ui = SpreadsheetApp.getUi();
var mastername = "Sheet4";
var master = ss.getSheetByName(mastername);
var hols = ss.getSheetByName('Sheet5');
// get the range for Master and the data also
var masterLR = master.getLastRow();
var masterRange = master.getRange(4,2,masterLR-3,8);
Logger.log("DEBUG: MASTER range = "+masterRange.getA1Notation());
var masterData = masterRange.getValues();
// get the last Column for hols for use in setting the update range
var holsLC = hols.getLastColumn();
Logger.log("DEBUG: the last column on Hols = "+holsLC);
// display event objects - two mwthods displayed here
// Method#1
Logger.log(JSON.stringify(e))
// Method#2
// var debug_e = {authMode: e.authMode,range: e.range.getA1Notation(),source: e.source.getId(),user: e.user,value: e.value,oldValue: e. oldValue};
//Logger.log("DEBUG: AuthMode: "+debug_e.authMode+", Range: "+debug_e.range+", source: "+debug_e.source+", user: "+debug_e.user+", value: "+debug_e.value+", old value: "+debug_e.oldValue);
// get the column, row and sheet name of the edited cell
var editColumn = e.range.getColumn();
var editRow = e.range.getRow();
var editSheet = e.range.getSheet().getSheetName();
Logger.log("DEBUG: editted Column = "+editColumn+", editted Row = "+editRow+", edited Sheet = "+editSheet);
// set variable
var firstdatarow = 4;
// create if statement
// iF the edited row is between firstdatarow and the last row
// AND
// IF the edited column is Column#1 (Column A)
// AND
// IF the edited sheet is Master
if (firstdatarow < editRow && editRow < masterLR && editColumn == 1 && e.value == "Y" && editSheet == mastername) {
// do something
Logger.log("DEBUG: do something");
// get the employee name
var empname = masterData[(+editRow-4)][0];
Logger.log("DEBUG: The employee name is "+empname);
// create an alert to confirm
var result = ui.alert(
'WARNING!! You are about to Update the holiday record for '+empname,
'Are you sure you want to continue?',
ui.ButtonSet.YES_NO);
// Process the user's response.
if (result == ui.Button.YES) {
// User clicked "Yes".
ui.alert('Confirmation received.');
// build the data to be pasted to Sheet5
var holsData = [];
for (var i = 1; i<54;i++){
for (var x = 1; x<8;x++){
holsData.push(masterData[(+editRow-1)][x]);
}
Logger.log("DEBUG: hols = "+holsData+", length = "+holsData.length);
}
Logger.log("DEBUG: Progressive holsData = "+holsData+", length = "+holsData.length);
// create the range for Sheet5
var target = hols.getRange(editRow,2,1,(+holsLC-1));
Logger.log("DEBUG: the target range = "+target.getA1Notation());
// update the values to Sheet5
target.setValues([holsData]);
//Removes "Y" to stop code repeating
master.getRange(editRow,editColumn).clearContent();
} else {
// User clicked "No" or X in the title bar.
ui.alert('Permission denied.');
//Removes "Y" to stop code repeating
master.getRange(editRow,editColumn).clearContent();
}
}
else
{
// don't do anything
Logger.log("don't do anything");
}
}
