Gappscript customer menu to trigger copy range to range of another spreadsheet for loop-CodePudding

What I am trying to do is transfer rows depending on the value in column p starting at row number 7. If cell in column P has a value of " Order" then copy that row from column B to Q to a completely separate already made spreadsheet. I have the script written in the target sheet.

Currently my script does loop through the row and will console.log the data I need... My issue is I have tried multiple things to then write the data to the correct range and can't figure it out.. I need to write the data to starting at row7 columnB... could use a little help..

function transferMonth() {
 

// SETTING UP THE LAST MONTH SHEET TO PULL NON SOLD DATA FROM
  const lastmonthSheetss = SpreadsheetApp.openById("ID OF SPREADSHEET").getSheetByName("CDJR");
  const lastSourceRow = lastmonthSheetss.getLastRow();
  const sourceRange = lastmonthSheetss.getRange(7, 2, lastSourceRow, 15);
  const sourceData = sourceRange.getValues();

// SETTING UP THE SHEET WHERE WE WANT TO TRANSFER LAST MONTHS DATA TO
  const targetsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TestCopy");
  const lastRow = targetsheet.getLastRow();
  const lastCol = targetsheet.getLastColumn();
  var sdata = [];

  // KEY FOR ACCESSING THE DATA PULLED FROM LAST MONTHS SHEET
  //console.log (sourceData[0]);
  //console.log(sourceData[0][1]);
  //console.log(sourceData[0][3]);
  //console.log(sourceData[0][5])
  //console.log(sourceData[0][14])
  //console.log(sourceData[1][1]);
  //console.log(sourceData[1][14]);

  //SETTING UP PLACE TO STORE VALUES THAT NEED COPIED

  for (i = 0; i < sourceData.length; i  ) {
    if (sourceData[i][14] === 'Order') {
      sdata.push.apply(sdata, lastmonthSheetss.getRange(i   7, 2, 1, 15).getValues());
      sdata.push(i);
       }
   console.log(sdata)
  }
targetsheet.getRange(7,2).setValues(sdata);
}

CodePudding user response：

You can start with this.

Script:

function transferMonth() {
  // SETTING UP THE LAST MONTH SHEET TO PULL NON SOLD DATA FROM
  const lastmonthSheetss = SpreadsheetApp.openById("ID OF SPREADSHEET").getSheetByName("CDJR");
  const lastSourceRow = lastmonthSheetss.getLastRow();
  const sourceRange = lastmonthSheetss.getRange(7, 2, lastSourceRow, 15);
  const sourceData = sourceRange.getValues();

  // SETTING UP THE SHEET WHERE WE WANT TO TRANSFER LAST MONTHS DATA TO
  const targetsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TestCopy");
  const lastRow = targetsheet.getLastRow();
  // use filter to only get the data with 'Order' as the 15th column
  var sdata = sourceData.filter(x => x[14] === 'Order');

  // always write starting at B7
  targetsheet.getRange(7, 2, sdata.length, sdata[0].length).setValues(sdata);
}

Sample Data:

Initial target sheet:

Output:

Note:

Your main issues were the wrong usage of setValues and the pushed index on the array, aside from that, your code should still be workable.