Google spreadsheet :Copying row from a range based on a cell value in that row

Question

I need to call a function which will copy all rows with quantity more than zero from the first tab (Ordering guide) and paste data in the second tab (Purchasing Order) in the same spreadsheet. See shared document.

https://docs.google.com/spreadsheets/d/1Ajccn3ZYtmd8WTFI3baDUd5eyRfCDXa1-cQJt7RGObE/edit?usp=sharing

Can someone please help me with this. I wrote the following code but itsn't working.

function AddLines(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var r = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();

  if(s.getName() == "Ordering guide" && r.getColumn() == 8 && r.getValue()>0) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Purchase Order");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);
  }
}

Waqar Ahmad Waqar Ahmad · Accepted Answer · 2017-01-18T09:21:56

I made a copy of your spreadsheet and tried to fix the script. Here is the new copy of spreadsheet with fixed code and works well.

Copy of PO Generation Spreasheet

Here is the fixed code.

//Generate Purchase Order
function generatePO() {
  var ss = SpreadsheetApp.getActive();
  var ogSheet = ss.getSheetByName('Ordering guide');
  var poSheet = ss.getSheetByName('Purchase Order');
  var lookupData = ogSheet.getRange(5, 1, ogSheet.getLastRow()-4, 9).getValues();
  var targetData = [];
  for(var i=0; i<lookupData.length; i++){
    if(lookupData[i][7] && lookupData[i][7] > 0){
      targetData.push(lookupData[i]);
    }
  }
  poSheet.getRange(5, 1, 50-4, 9).clearContent();
  poSheet.getRange(5, 1, targetData.length, 9).setValues(targetData);
  poSheet.activate();
}

//Reset Quantities
function resetQuantity(){
  var ss = SpreadsheetApp.getActive();
  var ogSheet = ss.getSheetByName('Ordering guide');
  var qtyRange = ogSheet.getRange(5, 8, ogSheet.getLastRow()-4, 1).clearContent();
}

Google spreadsheet :Copying row from a range based on a cell value in that row

1 Answers