CODEWITHSUNDEEP
google-apps-script
NSaini
NSaini

Reputation: 11

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

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);
  }
}

Upvotes: 1

Views: 209

Answers (1)

Waqar Ahmad
Waqar Ahmad

Reputation: 3732

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();
}

Upvotes: 1

Related Questions