Google Script to copy specific columns from one sheet to another sheets last row onEdit

0
votes

I am looking for some help on google app scripting. Currently I have a spreadsheet that has 3 sheets. First sheet is data that comes from a google form. Second sheet is an "open" tab and third is a "closed" tab.

When data comes into the spreadsheet from the form it has only the columns that the form has. The "open" sheet has a combination of data from the form (sheet 1) and some additional cells to add more information to by me and not the person submitting the form. Because of this column miss-match I cannot just copy a whole row from sheet 1 to sheet 2. This also needs to be done onEdit trigger. So when an edit trigger fires, I want to store that rows data into memory and then copy column B to the last row of tab 2, column C. Column C > E, column D > B. etc..

How would I store the whole row into memory (an array I presume), and then copy only specific cells, in a specific order into the last row of a different sheet?

Hope that makes sense :-/

2
google-apps-scriptgoogle-sheets

2 Answers

2
votes

As you said, you'll have to use arrays to get data and write it back to another sheet in your selected order. This is pretty basic manipulation and there are many ways to achieve it. One way that is very easy to understand is as follow :

  • get row data in an array
  • pick up each value one by one and store in another array
  • write back to the last row +1 on another sheet.

Note that arrays are indexed starting from 0 , rows and columns start from 1 and A so you'll have to do some math !

function copyRow(){
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var rowIdx = sheet.getActiveRange().getRowIndex();
  var rowValues = sheet.getRange(rowIdx,1,1,sheet.getLastRow()).getValues();
  Logger.log(rowValues);
  var destValues = [];
  destValues.push(rowValues[0][0]);// copy data from col A to col A
  destValues.push(rowValues[0][2]);// copy data from col C to col B
  destValues.push(rowValues[0][1]);// copy data from col B to col C
  destValues.push(rowValues[0][3]);// copy data from col D to col D
  // continue as you want
  var dest = ss.getSheets()[1];//go on chosen sheet (here is the 2cond one)
  dest.getRange(dest.getLastRow()+1,1,1,destValues.length).setValues([destValues]);//update destination sheet with selected values in the right order, the brackets are there to build the 2D array needed to write to a range
}

If you want that function to run on edit then just call it onEdit() and you're done !

0
votes

Source: https://stackoverflow.com/a/64297866/14427194

function onEdit(e) {
  //e.source.toast("Entry");
  //console.log(JSON.stringify(e));
  const sh=e.range.getSheet();
  if(sh.getName()=="Sheet1" && e.range.columnStart==20 && e.value=="TRUE") {
    const tsh=e.source.getSheetByName('Sheet2');
    const nr=tsh.getLastRow()+1;
    sh.getRange(e.range.rowStart,1,1,12).moveTo(tsh.getRange(nr,1,1,12));
    sh.getRange(e.range.rowStart,16,1,4).moveTo(tsh.getRange(nr,16,1,4));
    sh.deleteRow(e.range.rowStart);
  }
}