Delete blank rows in spreadsheet using Google scri

问题:

Spreadsheet-1: Data present in Spreadsheet-1,

Name     apple  android   windows    linux
Germany    3      4         6          7
America    4      1         6          2
Sweden     1      6         1          6
Paris      5      0         2          4

Spreadsheet-2: Data present in Spreadsheet-2,

Date   Name    apple  android   windows    linux

I am able to copy the data from spreadsheet1 to spreadsheet2 by using the below google script.

function Daily() {
  var SpreadSheetKeyA = "mykey1";
  var SpreadSheetKeyB = "mykey2";
  var sheet1 = SpreadsheetApp.openById(SpreadSheetKeyA).getSheetByName("Showstopper");
  var sheet2 = SpreadsheetApp.openById(SpreadSheetKeyB).getSheetByName("Daily");
  var data = sheet1.getRange(5,11,40,6).getValues();
  var time = new Date ().toJSON().slice(0,10);;
  for (var r = 0; r < data.length; r++) {
    data[r].unshift(time);
    sheet2.appendRow(data[r]);
    }
}

The data present in spreadsheet1 is dynamic i.e, the number of rows can vary. Now whenever I run the script again so as to update the spreadsheet2 data is being appended after blank rows. I would like enhance the above script so that it should avoid blank rows or only copy rows with data.

Can anyone help me with this please

回答1:

This should work if all blanks are at the bottom of the source and there is nothing else below the data.

function triggerOnTime() {
  var SpreadSheetKeyA = "MY key";
  var SpreadSheetKeyB = "MY key";
  var sheet1 = SpreadsheetApp.openById(SpreadSheetKeyA).getSheetByName("Source Name");
  var sheet2 = SpreadsheetApp.openById(SpreadSheetKeyB).getSheetByName("Target Name");
  var startRow = 5;
  var data = sheet1.getRange(startRow,11,sheet1.getLastRow() - startRow + 1,5).getValues();
  var time = new Date ().toJSON().slice(0,10);;
  for (var r = 0; r < data.length; r++) {
    data[r].unshift(time);
    }
  if(data.length > 0 ) {
    sheet2.insertRowsAfter(sheet2.getLastRow(), data.length);
    sheet2.getRange(sheet2.getLastRow()+1, 1, data.length, data[0].length).setValues(data);
    }
}

If for whatever reason the above conditions cannot be met and you need to hardcode the number of rows to 40 you might try the following:

function triggerOnTime() {
      var SpreadSheetKeyA = "MY key";
      var SpreadSheetKeyB = "MY key";
      var sheet1 = SpreadsheetApp.openById(SpreadSheetKeyA).getSheetByName("Source Name");
      var sheet2 = SpreadsheetApp.openById(SpreadSheetKeyB).getSheetByName("Target Name");
      var startRow = 5;
      var data = sheet1.getRange(5,11,40,6).getValues();
      var time = new Date ().toJSON().slice(0,10);;
      for(var i = data.length - 1; i > -1; i--) {
        if(data[i].join("").length == 0) data.splice(i, 1);
      }
      for (var r = 0; r < data.length; r++) {
        data[r].unshift(time);
        }
  if(data.length > 0 ) {
    sheet2.insertRowsAfter(sheet2.getLastRow(), data.length);
    sheet2.getRange(sheet2.getLastRow()+1, 1, data.length, data[0].length).setValues(data);
    }
    }