Context

I'm using Google Apps Scripts to automate some things in Google Sheets. Currently, I can copy the column data for every row without issue. However, I only need to copy rows that aren't empty in a specific column (G) and can't figure out where/how to implement the conditional.

Goal

1. For each row in the source sheet, check if cell in column G is empty.
2. If cell is not empty, copy parts of that row to other sheet.

Here is the current script:

function theme_copy_WIP () {
  let workbook = SpreadsheetApp.getActiveSpreadsheet(); // get the active sheets workbook
  let auditSheet = SpreadsheetApp.getActiveSheet(); // active sheet of workbook to copy from (source)
  let auditRangeList = auditSheet.getRangeList(["A2:A", "B2:B", "C2:C", "E2:E", "G2:G", "H2:H"]); //Range of columns to copy data from 

  let themeSheet = workbook.getSheetByName("Themes"); //themes sheet to paste into (destination)
  let destRangeList = themeSheet.getRangeList(["A2:A", "B2:B", "C2:C", "D2:D", "E2:E", "F2:F"]); //Range of destination columns to paste into
  for( let i=0; i<auditRangeList.getRanges().length; i   ) {
    let auditRange = auditRangeList.getRanges()[i];
    let destRange = destRangeList.getRanges()[i];
    auditRange.copyTo(destRange, {contentsOnly: true});
    }  
}

CodePudding user response：

Instead of using rangeList, get the full range, modify it and set back the modified range.

const out = auditSheet
  .getRange("A2:H" auditSheet.getLastRow())
  .getValues()
  .reduce((acc,[a,b,c,d,e,f,g,h]) => (g && acc.push([a,b,c,e,g,h]),acc),[])
themeSheet
  .getRange(2,1,out.length, out[0].length)
  .setValues(out)

CodePudding user response：

Copy Values

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const ssh = ss.getActiveSheet();
  const svs = ssh.getRange(2, 1, ssh.getLastRow() - 1, ssh.getLastColumn()).getValues();
  const tsh = ss.getSheetByName("Sheet0");
  const tlr = tsh.getLastRow();
  if (tlr > 1) {
    tsh.getRange(2, 1, tsh.getLastRow() - 1, 6).clearContent();
  }
  let tvs = svs.map(r => {
    if (r[6]) {
      return [r[0], r[1], r[2], r[4], r[6], r[7]];
    }
  }).filter(r => r );
  if (tvs && tvs.length > 0) {
    tsh.getRange(2, 1, tvs.length, tvs[0].length).setValues(tvs);
  }
}