CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Parker Alexander Case
Parker Alexander Case

Reputation: 13

Google Spreadsheets (app-script) copying a row of information to another spreadsheet

I need to, if any information is entered into column 5, copy that row of data to another spreadsheet. The first if() in my function is for something else, it sorts the data when any info is entered into column 4, which works, but I need the other if() to work, but it doesn't, I have looked at other questions, but I can't find an answer that will work in my situation.

function onEdit(event) {
  var sheet=event.source.getActiveSheet();
  var editedCell=sheet.getActiveCell();
  var columnToSortBy=4;
  var columnToArchive=5;
  var tableRange= "A2:F!";

  if(editedCell.getColumn() == columnToSortBy){
    var range=sheet.getRange(tableRange);
    range.sort( {column : columnToSortBy} );
  }
  else{
    if(editedCell.getColumn() == columnToArchive){
      var rowToBeMoved=editedCell.getRow();
      var rangeToBeMoved=sheet.getRange("A" + rowToBeMoved + ":F" + rowToBeMoved);
      var values=rangeToBeMoved.getValues();
      var archiveSpreadSheet=SpreadsheetApp.openById("0AroBvchobu2edHNXQ3ZUQjI5TWJtWWZwa1UtcExPNnc");
      var archiveSheet=archiveSpreadSheet.getSheetByName("archive");
      archiveSheet.appendRow(values);
    }
  }
}

I have identified that the problem is with the last three lines within the second if block. It seems that after the line "var values=rangeToBeMoved.getValues();", the script stops running because if a I put a Browser.msgBox("hi") after that line or anywhere before, that message appears, but if I put it after the next line, it does not appear. The problem is with the .openById()? HELP!

Upvotes: 1

Views: 2004

Answers (1)

Jacob Jan
Jacob Jan

Reputation: 1197

This piece of code is working:

UPDATE: 10-01-2013
You need to use the columnIndex and not the getColumn:

function onEdit(event) {
  // set items for current spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var row = sh.getActiveRange().getRowIndex();
  var col = sh.getActiveRange().getColumnIndex();
  var columnToSortBy=4;
  var columnToArchive=5;
  var tableRange= "A2:F!";

  if(col == columnToSortBy){
    var range=sh.getRange(tableRange);
    range.sort( {column : columnToSortBy} );
  }

  if(col == columnToArchive){   
    var rangeToBeMoved=sh.getRange("A" + row + ":F" + row);
    var values=rangeToBeMoved.getValues();
    var archiveSpreadSheet=SpreadsheetApp.openById("your key");
    var archiveSheet=archiveSpreadSheet.getSheetByName("Sheet1");        
    archiveSheet.appendRow(values[0]);
  }
}

The appendRow only accepts a 1D array. The getRange will result in a 2D array. Added a zero (between square brackets) will make it a 1D array.

Upvotes: 1

Related Questions