0
votes

NEEDLES TO SAY, I am very new to this, but trying my hardest to figure all this out. I have a script right now which actively removes all empty rows from an entire workbook. But I'm hoping someone can assist is narrowing this down to a single sheet within that workbook.

function removeEmptyRows() {
    SpreadsheetApp.getActive()
        .getSheets()
        .forEach(function (s) {
            c = 0;
            s.getRange(1, 1, s.getMaxRows(), s.getMaxColumns())
                .getValues()
                .forEach(function (r, j) {
                    if (r.toString()
                        .replace(/,/g, "")
                        .length == 0) {
                        s.deleteRow((j += 1) - c)
                        c += 1;
                    }
                })
        })
}

Ideally I would like for this to remove only blank rows on one sheet within my workbook called 'Racing Results'. The reason I'm need this, is due to how the spreadsheet is setup and having multiple rows merged together. So when I copy these results over to a different sheet, there are gaps between them and I'd like them removed. Here is the script I'm using to copy the data to another sheet.

function Copy() {

 var sss = SpreadsheetApp.openById('18cl69Id4saI455wk__-PhvfxXZa7iWlQpoiRKqBz6bU');
 var ss = sss.getSheetByName('Score Card');
 var range = ss.getRange('A32:E36');
 var data = range.getValues();
 var tss = SpreadsheetApp.openById('18cl69Id4saI455wk__-PhvfxXZa7iWlQpoiRKqBz6bU');
 var ts = tss.getSheetByName('Race Results');

 ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

}

If not to throw one final monkey wrench into this colossal mess, I have been attempting to copy two different cell ranges within the same script and I feel like this isn't possible because only the latter one gets copied and the initial one is discarded. Here is the other copy script I am using which runs before the one above this.

function Copy() {

var sss = SpreadsheetApp.openById('18cl69Id4saI455wk__-PhvfxXZa7iWlQpoiRKqBz6bU');
 var ss = sss.getSheetByName('Score Card');
 var range = ss.getRange('A1:A1');
 var data = range.getValues();
 var tss = SpreadsheetApp.openById('18cl69Id4saI455wk__-PhvfxXZa7iWlQpoiRKqBz6bU');
 var ts = tss.getSheetByName('Race Results');

 ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

}

Score Card Screenshot

In a perfect world, what I'm trying to get setup is the following. Above is a screenshot of the scorecard we are using. I am wanting to copy the Current Date (A1) to the 'Racing Results' sheet, then I want to copy the final score with the team names (A32:E36) and move them to the 'Racing Results' sheet right under it. Once that has been done, I want to remove the empty rows between the results because as of right now this is how it looks when copying over. (see image below)

Race Results Screenshot

Thanks in advance to anyone who is able to assist with any of this in any way shape or form.

Edit: Removing Empty Rows from a spreadsheet is functioning as intended. Still having issues with Copying multiple times in the same action even when giving them different names. Here is my updated script.

function CopyDate() {
  var sss = SpreadsheetApp.openById('18cl69Id4saI455wk__-PhvfxXZa7iWlQpoiRKqBz6bU');
  var ss = sss.getSheetByName('Score Card');
  var range = ss.getRange('A1:A1');//This range is only one cell
  var data = range.getValues();
  var tss = SpreadsheetApp.openById('18cl69Id4saI455wk__-PhvfxXZa7iWlQpoiRKqBz6bU');
  var ts = tss.getSheetByName('Race Results');  
  ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);
}

function CopyScore() {
  var sss = SpreadsheetApp.openById('18cl69Id4saI455wk__-PhvfxXZa7iWlQpoiRKqBz6bU');
  var ss = sss.getSheetByName('Score Card');
  var range = ss.getRange('A32:E36');
  var data = range.getValues();
  var tss = SpreadsheetApp.openById('18cl69Id4saI455wk__-PhvfxXZa7iWlQpoiRKqBz6bU');
  var ts = tss.getSheetByName('Race Results');  
  ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);
}

function delBlankRows(shtname){
  var shtname=shtname || 'Race Results';
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName(shtname);
  var rg=sh.getRange(1,1,sh.getMaxRows(),sh.getLastColumn());
  var vA=rg.getValues();
  var n=0;
  for(var i=0;i<vA.length;i++){
    if(!vA[i].join("")){
      sh.deleteRow(i-n+1);
      n++;
    }
  }
}

function Sleep(milliseconds) {
  var start = new Date().getTime();
  for (var i = 0; i < 1e7; i++) {
    if ((new Date().getTime() - start) > milliseconds){
      break;
    }
  }
}

function SaveRaceResults() {
  CopyDate();
  Sleep(5000);
  CopyScore();
  Sleep(5000);
  delBlankRows();
}
1
Do some refactoring of the first function - create a method that receives a sheet as the input and trims its rows, e.g. function removeRowsFromSheet(sheet). (The code for this is basically written already). Then you use this function in both the code above, and the code you want to use. As for your copy scripts, they have the same name. That means the one written last replaces the previous definition of that name - only one exists to the executing code. - tehhowch

1 Answers

0
votes

Deleting All Blank Rows on a Sheet

function delBlankRows(shtname){
  var shtname=shtname || 'Sheet1';
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName(shtname);
  var rg=sh.getRange(1,1,sh.getMaxRows(),sh.getLastColumn());
  var vA=rg.getValues();
  var n=0;
  for(var i=0;i<vA.length;i++){
    if(!vA[i].join("")){
      sh.deleteRow(i-n+1);
      n++;
    }
  }
}

Copy function:

function Copy() {
  var sss = SpreadsheetApp.getActive();
  var ss1 = sss.getSheetByName('Score Card');
  var range1 = ss1.getRange('A1');//This range is only one cell
  var data1 = range1.getValues();
  var ts1 = sss.getSheetByName('Race Results');  
  ts1.getRange(ts1.getLastRow()+1, 1, data1.length, data1[0].length).setValues(data1);
  var ss2 = sss.getSheetByName('Score Card');
  var range2 = ss2.getRange('A32:E36');
  var data2 = range2.getValues();
  var ts2 = sss.getSheetByName('Race Results');  
  ts2.getRange(ts2.getLastRow()+1, 1, data2.length, data2[0].length).setValues(data2);
}