CODEWITHSUNDEEP
google-apps-script
Scott Phillips
Scott Phillips

Reputation: 1

Google Apps Script Function generating an extra blank row

I have an Apps Script function that manipulates rows of data in a sheet by first repositioning them and then transposing column data into rows. Everything works well except that I end up with an extra blank row between each block of 48 rows of data.see sheet 2 "Raw Data"

Here is the portion of the function code where something is going wrong.

// find the initial last row in the 'Raw Data' sheet
    var sheet2LastRow = sheet2.getLastRow();
    Logger.log(sheet2LastRow);                                        // 47
    var numRows = (sheet2LastRow - 1);                              
    Logger.log(numRows);                                              // 46
    
    
// find the Current Row to process and move to the destination row 
    for (var CR = 1 ; CR < sheet2LastRow; CR++) {                    
    Logger.log(CR);                                                  //   1 > 46
    sheet2.getRange((CR + 1), 1, 1, 59).moveTo(sheet2.getRange((CR * 49), 1)); 

    
      
 // transpose data from rows to columns for Data Studio     
    sheet2.getRange(1, 12, 1, 48).copyTo(sheet2.getRange((CR * 49), 10), SpreadsheetApp.CopyPasteType.PASTE_VALUES, true);           // Dimension
    sheet2.getRange((CR * 49), 12, 1, 48).copyTo(sheet2.getRange((CR * 49), 11), SpreadsheetApp.CopyPasteType.PASTE_VALUES, true);   // Opportunity
    sheet2.getRange((CR * 49), 1, 1, 9).copyTo(sheet2.getRange((CR * 49), 1, 48, 9), SpreadsheetApp.CopyPasteType.PASTE_VALUES);     // Respondent Info

Upvotes: 0

Views: 109

Answers (1)

Tanaike
Tanaike

Reputation: 201358

I think that the reason of your issue is this line sheet2.getRange(row, 1, 1, 9).copyTo(sheet2.getRange(row, 1, 48, 9), SpreadsheetApp.CopyPasteType.PASTE_VALUES). In this line, the value is copied to 48 rows. But at the next loop, the value is copied to the row of (48 + 1).

In order to remove this issue, how about the following modification?

Pattern 1:

In this modification pattern, the for loop is modified by adjusting the row number.

From:

for (var CR = 1 ; CR < sheet2LastRow; CR++) {
  Logger.log(CR);                                                  //   1 > 46
  sheet2.getRange((CR + 1), 1, 1, 59).moveTo(sheet2.getRange((CR * 49), 1)); 
// transpose data from rows to columns for Data Studio     
  sheet2.getRange(1, 12, 1, 48).copyTo(sheet2.getRange((CR * 49), 10), SpreadsheetApp.CopyPasteType.PASTE_VALUES, true);           // Dimension
  sheet2.getRange((CR * 49), 12, 1, 48).copyTo(sheet2.getRange((CR * 49), 11), SpreadsheetApp.CopyPasteType.PASTE_VALUES, true);   // Opportunity
  sheet2.getRange((CR * 49), 1, 1, 9).copyTo(sheet2.getRange((CR * 49), 1, 48, 9), SpreadsheetApp.CopyPasteType.PASTE_VALUES);     // Respondent Info
}

To:

for (var CR = 1 ; CR < sheet2LastRow; CR++) {
  var row = (CR * 49) - (CR - 1);
  sheet2.getRange((CR + 1), 1, 1, 59).moveTo(sheet2.getRange(row, 1)); 
  sheet2.getRange(1, 12, 1, 48).copyTo(sheet2.getRange(row, 10), SpreadsheetApp.CopyPasteType.PASTE_VALUES, true);
  sheet2.getRange(row, 12, 1, 48).copyTo(sheet2.getRange(row, 11), SpreadsheetApp.CopyPasteType.PASTE_VALUES, true);
  sheet2.getRange(row, 1, 1, 9).copyTo(sheet2.getRange(row, 1, 48, 9), SpreadsheetApp.CopyPasteType.PASTE_VALUES);
}

Pattern 2:

In this modification pattern, the for loop is not modified. After all values were copied, the empty rows are deleted.

From:

sheet2.deleteRows(2, 47);
var sheet2NewLastRow = sheet2.getLastRow();

To:

sheet2.deleteRows(2, 47);
var values = sheet2.getRange("A2:DL").getValues();
for (var i = values.length - 1; i >= 0; i--) {
  if (values[i].every(e => !e.toString())) sheet2.deleteRow(i + 2);
}
var sheet2NewLastRow = sheet2.getLastRow();

Upvotes: 2

Related Questions