0

I created a script to delete blank rows from many spreadsheet files.

enter image description here

The script I created like this (Version 1) :

function delRow(id, name, startBlankRow, countBlankRow){
  SpreadsheetApp.openById(id).getSheetByName(name).deleteRows(startBlankRow, countBlankRow);
}

function bulkDelRow(){
  //try {
  var dataValue = [];
   dataValue = SpreadsheetApp.getActive()
  .getRange('List!A2:F')
  .getValues()
  .reduce(function(p,c){
    if (c[1] !== '' && c[2] !== '' && c[4] !== '' && c[5] !== '' || c[5] !== 0)
      //p.push(SpreadsheetApp.openById(c[1]).getSheetByName(c[2]).deleteRows(c[4],c[5]));
      p.push(!!delRow(c[1], c[2], c[4], c[5]));
    return p;
  }, []);
  //} catch (e) {
  //  Logger.log(e.toString());
  //}
  //Logger.log(spreadsheetID);
}
  • If all files have empty rows, the script to delete empty rows will succeed.

  • But if there is a file there is no row that is empty then an error.

    Those rows are out of bounds

Updated, I try to use another script by using the getMaxRows function,

function deleteBlankRow() {
  var dataValue = [];
  dataValue = SpreadsheetApp.getActiveSpreadsheet()
  .getSheetByName('List')
  .getRange('A2:C')
  .getValues()
  .reduce(function (p, c) {
    var ssID = SpreadsheetApp.openById(c[1]);
    var sheet = ssID.getSheetByName(c[2]);
    var maxRow = sheet.getMaxRows();
    var startBlankRow = sheet.getRange(1, 3).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow() + 1;
    var countBlankRow = maxRow - startBlankRow;
    if (countBlankRow !== 0 && c[0] !== '' &&  c[1] !== '' && c[2] !== '') {

        p.push(ssID.getSheetByName(sheet).deleteRows(startBlankRow, countBlankRow));
               return p;
      } else {
        Logger.log('no blank row');
            }

}, []);

}

but after I run the result it still has an error.

TypeError: Cannot call method "deleteRows" of null. (line 15, file "deleletBlankRow")

  • I apologize for my poor English skill. I couldn't understand what you want to do from your question and script. Can I ask you about to skip files that have no blank rows? And can you provide the sample input and output you want? Of course, please remove your personal information. – Tanaike Jun 19 at 2:52
  • I also apologize for my unclear question. I will update the question again – Lutfi Creativesys Jun 19 at 6:22
  • I have updated my question, hopefully my explanation is quite clear. Sorry, I'm still not good at speaking English. – Lutfi Creativesys Jun 19 at 6:42
  • Thank you for replying and adding the information. Unfortunately, I cannot still understand what you want to do from your question and script. I apologize for my poor English skill. If I could correctly understand, I would like to think of the solution. I deeply apologize I cannot resolve your issue soon. – Tanaike Jun 19 at 9:09
  • the title question has updated again.. – Lutfi Creativesys Jun 19 at 13:17
0

Add a conditional statement to check if the sheet rows are greater than the last row, if they are proceed otherwise do nothing (or something different to delete rows)

There are several ways to do this

  1. If your sheet (first image) is already reporte the number of black rows, if there aren't blank rows, just skip that file.
  2. Use getMaxRows() and getLastRow() to the the number of rows on sheet and the last row of the data range, if they are the same, the skip that file.

NOTE: As there isn't clear description of what the script does and the variable used I'm not sure how to make a simple fix to the script.

  • I have tried using another script by adding the getmaxrows function but the result is still an error. hastebin.com/nuporiheka.js – Lutfi Creativesys Jun 19 at 15:23
  • @LutfiCreativesys To provide additional details or your attempts to apply a solution, please update your question. – Rubén Jun 19 at 15:37
  • I have updated the details of the question. – Lutfi Creativesys Jun 20 at 2:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.