Question

0

100

Views

Copy only non empty cells

I have this code which copies the data from a column and transfers it to another sheet at the bottom of the table. This works fine but the problem is it copies the whole column including the blank one. I just want to limit the cells to be copied to cells with data only. Here's the code:

function addTitles() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); 
  let sourceSheet = spreadSheet.getSheetByName('Data Entry'); 
  let sourceRange = sourceSheet.getDataRange(); 
  let sourceValues = sourceRange.getValues();
  sourceValues = sourceValues.slice(1).map(row => row.slice(10,10+1));
  let sheetToAdd = spreadSheet.getSheetByName('Cost of Goods'); 

  let rowCount = sourceValues.length;
  let columnCount = sourceValues[0].length;

  let lastRow = sheetToAdd.getLastRow() + 1;

  let targetSheet = spreadSheet.getSheetByName('Cost of Goods');
  let targetRange = targetSheet.getRange(lastRow,1,rowCount,columnCount);
  targetRange.setValues(sourceValues);

  let fillDown = sheetToAdd.getRange(lastRow, 2, lastRow-1, columnCount);
  sheetToAdd.getRange("B4:CV4").copyTo(fillDown);
}

Here's the sample sheet: https://docs.google.com/spreadsheets/d/1AuzIZqIPbnqqZ0VirAOwY--7ISU22tAnc3ojsVwZRn4/edit?usp=sharing

about 4 years ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-07-10T22:46:14.508Z

Issue:

If I understand you correctly, you want to filter out rows from sourceValues which have an empty string as its value (you are only retrieving a column, so each row only has one value), using filter.

Solution:

If that's the case, you could just replace this:

sourceValues = sourceValues.slice(1).map(row => row.slice(10,10+1));

With this:

sourceValues = sourceValues.slice(1).map(row => row.slice(10,10+1))
                           .filter(row => String(row[0]));

Reference:

filter

0

100