Automatically copy and paste values from one google sheet to another

Question

I have 2 sheets within my spreadsheet per the following link:

The first, called "Events/Incidents" records all event data ranging from complaints, compliments, accidents and near misses. If there is an event requiring an insurance claim, the user will tick the box in column Y which will require another user to file the claim. I have therefore created the second sheet titled "Insurance Claims". This will be like a Gantt chart timetabling progress on the insurance claim.

Once the initial user ticks the box in column Y of the "Events/Incidents" I want values from a number of cells from the corresponding row to be copied and pasted into the "Insurance Claims sheet.

I am still learning to write code so I tend to pinch code from other Stack Overflow answers but I am struggling with this one. Here is my current attempt but I feel like I am missing critical parts to the function.

//Data from Events/Incidents Sheet to Insurance Claims Sheet
function dataInsuranceClaimsSheet() {

  // setup spreadsheet and sheets
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sourcesheetname = "Events/Incidents";
  var source = spreadsheet.getSheetByName(sourcesheetname);
  var targetsheetname = "Insurance Claims";
  var target = spreadsheet.getSheetByName(targetsheetname);
  var trigger = source.getRange(3, 1, 1, 25) //column Y of Events/Incidents sheet

  // get Form Response
  //Logger.log(JSON.stringify());
  var row = source.range.getRow();
  var range = source.getRange(row, 1, 1, 26);
  
  //Logger.log("DEBUG: The range is "+range.getA1Notation());  
  var values = range.getValues();  
  var sourceDate = values[0][1]
  var sourceId = values[0][2];
  var sourceBranch = values[0][3];
  var sourceEmployee = values[0][5]
  var sourceFleet = values[0][7];
  var sourceRoute = values[0][8];
  var sourceDescription = values[0][17]
  var targetDate = values[0][1]
  var targetId = values[0][2];
  var targetBranch = values[0][3];
  var targetEmployee = values[0][4]
  var targetFleet = values[0][5];
  var targetRoute = values[0][6];
  var targetDescription = values[0][7]  
     
     
   //update the Insurance Claims sheet
   var targetupdate = [];
   var targetfinal=[];

   // push the values to a blank array
   targetupdate.push(sourceDate); // Date
   targetupdate.push(sourceId); // ID
   targetupdate.push(sourceBranch);// Branch
   targetupdate.push(sourceEmployee); // Employee
   targetupdate.push(sourceFleet); // Fleet
   targetupdate.push(SourceRoute);// Route
   targetupdate.push(sourceDescription);// Description
     
   // if column Y of the Events/Incidents sheet = TRUE then copy the data from the Events/Incidents sheet and paste into the defined cells of the Insurance Claims sheet
   if (trigger == TRUE);
   targetfinal.push(targetupdate);

   // get Insurance Claims data
   var targetLR = target.getLastRow();
   var targetupdaterange = target.getRange(targetLR+1,1,1,8);
   Logger.log("DEBUG: The target range = "+targetupdaterange.getA1Notation());

   // update the form values to the Insurance Claims sheet
   targetupdaterange.setValues(targetfinal);

    }

I would really appreciate some help with this one.

A sheet doesn't have a range method: var row = source.range.getRow(); Your comment says // get Form Response. What kind of Form are you referring to? — Cooper

Iamblichus Iamblichus · Accepted Answer · 2019-12-16T11:35:17

I assume that:

You want to have a checkbox in each cell of column Y from sheet Events/Incidents.
You want that every time one of these checkboxes is checked, the data from the corresponding row gets appended to the sheet called Insurance Claims.

If that's the case, then you can use an onEdit trigger and, inside the function that is fired, check if:

The edited sheet is Events/Incidents.
The edited column is Y (index 25).
The edited row is above 2.
The checkbox is checked (true).

To check those conditions we need to know which column, row and sheet is edited, and we can get that information through the use of the event object.

Then, if those conditions are met, the data from the corresponding row needs to be retrieved. After that, because we don't want all the values from the original row to get copied, some of them have to be removed from the corresponding array. The method splice can be used for that. And finally, appendRow can be used to add the selected data to Insurance Claims. Your function could be something like this:

function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var row = range.getRow();
  var column = range.getColumn();
  var sourcesheetname = "Events/Incidents";
  var checkbox = range.getValue();
  if (sheet.getName() == sourcesheetname && column == 25 && row > 2 && checkbox == true) {
    var targetsheetname = "Insurance Claims";
    var target = e.source.getSheetByName(targetsheetname);
    var numCols = sheet.getLastColumn();
    var values = sheet.getRange(row, 1, 1, numCols).getValues()[0];
    values.splice(17)
    values.splice(8, 8)
    values.splice(5, 1)
    values.splice(3, 1); // Removing undesired values
    var lastRow = target.getLastRow();
    var lastCol = target.getLastColumn();
    target.appendRow(values); // Append new row  
  }
}

Bear in mind that if you already have an onEdit trigger in your code, you should add this code to the existing onEdit. Otherwise only one of them is gonna fire.

I hope this is of any help.

Automatically copy and paste values from one google sheet to another

1 Answers