使用Google脚本删除电子表格中的空白行 [英] Delete blank rows in spreadsheet using Google script

问题描述

Spreadsheet-1：数据存在于Spreadsheet-1中，

 名称apple android windows 
德国3 4 6 7 
美国4 1 6 2 
瑞典1 6 1 6 
巴黎5 0 2 4 
  

Spreadsheet-2：数据存在于Spreadsheet-2中，

 日期名称apple android windows linux 
  

我可以使用下面的Google脚本将数据从spreadsheet1复制到spreadsheet2。

 函数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 [r] .unshift（time）; 
 sheet2.appendRow（data [r]）; 
 
 
 
 
 
 
 
电子表格1中的数据是动态的，即数字的行可以变化。现在，无论何时我再次运行脚本以更新电子表格2数据正在追加空行之后。我希望增强上面的脚本，以避免空白行或只复制包含数据的行。
 
 
 
任何人都可以帮助我请 
 
解决方案
如果所有空白都位于数据源的底部并且数据下面没有其他内容， $ b 
  function triggerOnTime（）{
 var SpreadSheetKeyA =MY key; 
 var SpreadSheetKeyB =我的钥匙; 
 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 [r] .unshift（time）; （data.length> 0）{
 sheet2.insertRowsAfter（sheet2.getLastRow（），data.length）; 
} 
 if 
 sheet2.getRange（sheet2.getLastRow（）+ 1，1，data.length，data [0] .length）.setValues（data）; 
 
 
 
 
 
 
 $ b如果出于任何原因无法满足上述条件，您需要将行数硬编码为40，您可以尝试以下操作： 
 
 
 函数triggerOnTime（）{
 var SpreadSheetKeyA =我的钥匙; 
 var SpreadSheetKeyB =我的钥匙; 
 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。拼接（i，1）; （var r = 0; r  data [r] .unshift（time）; 
} 
; （data.length> 0）{
 sheet2.insertRowsAfter（sheet2.getLastRow（），data.length）; 
} 
 if 
 sheet2.getRange（sheet2.getLastRow（）+ 1，1，data.length，data [0] .length）.setValues（data）; 
} 
} 
  
 
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 
解决方案
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);
    }
    }


                        
这篇关于使用Google脚本删除电子表格中的空白行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！