The Code

I have a script in Google Sheets that takes data from a sheet called OUT and transfers it to a sheet called EXPORT plus it then increases the value of IN!B2 by 1.

function Transfer() {
var app = SpreadsheetApp;
var sss = app.getActiveSpreadsheet();

var out_sheet = sss.getSheetByName('OUT');
var out_sheet_lastrow = out_sheet.getLastRow();
var range_to_copy = out_sheet.getRange(2,1,out_sheet_lastrow,15).getValues();

var export_sheet = sss.getSheetByName('EXPORT');
var export_sheet_lastrow = export_sheet.getLastRow();

var cell = sss.getSheetByName('IN').getRange('B2');
var value = cell.getValue() * 1;
cell.setValue(value 1);

  { 
    export_sheet.getRange(export_sheet_lastrow 1,1,out_sheet_lastrow,15).setValues(range_to_copy);
  }
};

The problem

Since the range of rows in EXPORT will vary each time, I need to make sure there are no empty rows or otherwise importing to BigQuery gives me an error because of the imported blank row.

For example, this is the Sheet EXPORT before the data is transferred from OUT:

Sheet EXPORT before transfer of data:

While this is the Sheet EXPORT after the transfer:

Sheet EXPORT after transfer of data:

The Needed Solution:

Ideally I would like to modify the code to avoid the addition of this extra row. If this is not possible because it is added by default, then help in what would be the best solution to delete it each time the data is moved fro OUT to EXPORT so that when BigQuery tries to fetch the data there is no blank row at the end of the sheet.

CodePudding user response：

Description

Since you are starting from row 2 in the following getRange() you are actually getting 1 more row of data then actually contains data. If it had started with row 1 then your original code is correct. Your code should be corrected as follows

var range_to_copy = out_sheet.getRange(2,1,out_sheet_lastrow-1,15).getValues();

export_sheet.getRange(export_sheet_lastrow 1,1,out_sheet_lastrow-1,15).setValues(range_to_copy);

}