How can I ignore blank rows?

Viewed 36

I'm currently making a google spreadsheet to help with finances and have run into a problem.

I'm wanting to take a set of data, inputted by the user and move it to a separate master sheet for future comparison - IE user inputting expenses for the month, using a created button with the assigned script within the sheet to move said data away to said master sheet and the input sheet being cleared ready for the next month. The master sheet needs to retain past data imported/pasted over.

So far I've tried just using formula as I'm much more familiar with it - however, nothing seems to be what I'm looking for as if the data within the input sheet is changed, data in the master sheet is changed.

I've had a quick look into scripts which could do this but to be honest, I'm fairly lost. It's not something I've really touched before, though I have a very, very, very small amount of knowledge about it.

Below is what I've tried script-wise, it copies over things I need and retains information, however I have to set the sourceRange to a specific range to combat it copying over 994 cells total. Realistically, data would extend over the E15 mark, but would vary each time the script was used. The only thing I can assume would help is for it to ignore all blank rows and take up to the last value - but as stated, I have very limited knowledge and I have no idea where to start with it. Beyond this, I've tried videos, and just general searching but due to lack of knowledge, nothing seems to work.

function finances() {
 importRange(
   "1wVf02IgCTQ1dY2EIJhgbOYlAOi3q19rQN4VFuobiGHY",
   "TEST EXP!B9:E15",
   "1wVf02IgCTQ1dY2EIJhgbOYlAOi3q19rQN4VFuobiGHY",
   "ScriptSheet!B2:F"
 ); 
};

function importRange(sourceID, sourceRange, destinationID, destinationRangeStart) {

  const sourceSS = SpreadsheetApp.openById(sourceID);
  const sourceRng = sourceSS.getRange(sourceRange);
  const sourceVals = sourceRng.getValues();

  const destinationSS = SpreadsheetApp.openById(destinationID);
  const destStartRange = destinationSS.getRange(destinationRangeStart);
  const destSheet = destinationSS.getSheetByName(destStartRange.getSheet().getName())

  const destRange = destSheet.getRange(
    destStartRange.getLastRow(), // start row
    destStartRange.getColumn(), // start column
    sourceVals.length, // Row depth
    sourceVals[0].length // Col width
  );

  destRange.setValues(sourceVals);

};

Example

1 Answers

Replace

const sourceVals = sourceRng.getValues();

by

const sourceVals = sourceRng.getValues().filter(String);
Related