Question 298193 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – How can I remove the empty rows before saving the data?

I have a code and the link google sheet
the problem is when I fill in the data and then click on the button Submit at PrintForm sheet the data will be saved to PrintLog sheet but it includes the empty rows
so I would like to remove the empty rows before saving that data to RpintLog sheet but I don’t know how I can handle it, Looking forward to receiving your support

This is the input form

This is the output

Link google sheet

function Printting() {
  // We will add our code here. 
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // collect the data
  const sourceRange = ss.getRangeByName("clearRange");
  const clearRange = sourceRange.getValues().flat();
  const SOLA_sourceRange = ss.getRangeByName("SOLA");
  const SOLA_sourceVals = SOLA_sourceRange.getValues().flat();

  const SOLB_sourceRange = ss.getRangeByName("SOLB");
  const SOLB_sourceVals = SOLB_sourceRange.getValues().flat();

  const SOLC_sourceRange = ss.getRangeByName("SOLC");
  const SOLC_sourceVals = SOLC_sourceRange.getValues().flat();

  const SOLD_sourceRange = ss.getRangeByName("SOLD");
  const SOLD_sourceVals = SOLD_sourceRange.getValues().flat();

  const SOLE_sourceRange = ss.getRangeByName("SOLE");
  const SOLE_sourceVals = SOLE_sourceRange.getValues().flat();

  const SOLF_sourceRange = ss.getRangeByName("SOLF");
  const SOLF_sourceVals = SOLF_sourceRange.getValues().flat();

  const SOLG_sourceRange = ss.getRangeByName("SOLG");
  const SOLG_sourceVals = SOLG_sourceRange.getValues().flat();

  const SOLH_sourceRange = ss.getRangeByName("SOLH");
  const SOLH_sourceVals = SOLH_sourceRange.getValues().flat();
  // Gather current dts and user email.
  const date = new Date();
  const email = Session.getActiveUser().getEmail();
  const SOLA_data = [date, email, ...SOLA_sourceVals];
  const SOLB_data = [date, email, ...SOLB_sourceVals];
  const SOLC_data = [date, email, ...SOLC_sourceVals];
  const SOLD_data = [date, email, ...SOLD_sourceVals];
  const SOLE_data = [date, email, ...SOLE_sourceVals];
  const SOLF_data = [date, email, ...SOLF_sourceVals];
  const SOLG_data = [date, email, ...SOLG_sourceVals];
  const SOLH_data = [date, email, ...SOLH_sourceVals];
  // append the data
  const destinationSheet = ss.getSheetByName("PrintLog");
  destinationSheet.appendRow(SOLA_data);
  destinationSheet.appendRow(SOLB_data);
  destinationSheet.appendRow(SOLC_data);
  destinationSheet.appendRow(SOLD_data);
  destinationSheet.appendRow(SOLE_data);
  destinationSheet.appendRow(SOLF_data);
  destinationSheet.appendRow(SOLG_data);
  destinationSheet.appendRow(SOLH_data);
  // clear the source sheet rows
  sourceRange.clearContent();
  ss.toast("Success: Item Added to the data Log!");

};
Tags:

2

Answers


  1. 0

    Your script does not detect blank rows.

    The script shown below identifies the number of rows of data on PrintForm and uses this to set the getRange and getValues. This limits the data to ONLY rows with values and excludes any empty rows.

    The OP script is idiosyncratic, and doesn’t easily adapt to conversion.

    function updateLog() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var formSheet = ss.getSheetByName("PrintForm")
  var logSheet = ss.getSheetByName("PrintLog")

  // get the data from Form sheet
  // get the number of rows of data in Column B - SOL
  var solVals = formSheet.getRange("B6:B").getValues();
  var solLast = solVals.filter(String).length;
  //Logger.log("DEBUG: the number of rows of SOL data = "+solLast)

  var dataRange = formSheet.getRange(6,2,solLast,6)
  //Logger.log("DEBUG: the data range = "+dataRange.getA1Notation())
  var data = dataRange.getValues()
  
  // some email variables
  var date = new Date();
  var email = Session.getActiveUser().getEmail();
  // create a temporary array to hold the data to be pasted
  var dataArray = []
  
  // loop though the data
  for (var i=0;i<data.length;i++){
    var rowArray = []
    rowArray.push(date,email,data[i][0],data[i][1],data[i][2],data[i][3],data[i][4],data[i][5])
    dataArray.push(rowArray)
  }

  //Logger.log(dataArray)
  // get the last row in Log
  var logSheetLR = logSheet.getLastRow()
  //Logger.log("DEBUG: the last row of data on log sheet = "+logSheetLR)
  // get the target range on the Log sheet
  var logRange = logSheet.getRange((logSheetLR+1),1,dataArray.length,8)
  //Logger.log("DEBUG: the range in the log sheet = "+logRange.getA1Notation())

  // update the log sheet
  logRange.setValues(dataArray)

  // clear the source sheet rows
  dataRange.clearContent();
  ss.toast("Success: Item Added to the data Log!");
}
    Login or Signup to reply.
  2. 0

    Since you appear to have data in your first column sheet.getLastRow() is not useful to you. Appending rows is quite slow

    function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("SOLA");
  const osh = ss.getSheetByName("SOLB");
  const sr = 5;//data start row
  const vs = sh.getRange(sr, 1, getColumnHeight(2,sh,ss) - sr + 1, sh.getLastColumn()).getValues();
  const o = vs.map(([,b,c,d,e,f,g],i) => [new Date(),Session.getActiveUser().getEmail(),b,c,d,e,f,g])
  osh.getRange(osh.getLastRow() + 1, 1, o.length, o[0].length).setValues(o);
}

function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var col = col || sh.getActiveCell().getColumn();
  var rcA = [];
  if (sh.getLastRow()) { rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse(); }
  let s = 0;
  for (let i = 0; i < rcA.length; i++) {
    if (rcA[i].toString().length == 0) {
      s++;
    } else {
      break;
    }
  }
  return rcA.length - s;
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search