CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Tyler Shannon
Tyler Shannon

Reputation: 309

How to archive certain rows to another sheet

I am trying to move my team's productivity to another sheet via Apps Script.

On the sheet "Prod" you can see the weekly productivity the team has put in. The "Approved" column has checkboxes where the work can be manually verified. Prod Sheet

The second sheet is the "Archives" sheet. Where the Apps Script will move all of the responses from the "Prod" sheet over there. Archives Sheet

Apps Script Code

function archive() {
  // Get the information
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Prod');
  var a = ss.getSheetByName('Archives');
  var data = s.getRange('A2:F').getValues();
  
  // Archive the results
  for (i in data) {
    var write = data[i];
    a.appendRow(write);
  }
  
  // Clear the results
  s.getRange('A2:E').clear();
  s.getRange('F2:F').uncheck();
}

The Problem

The code works perfectly fine, however, when it is moving the "Prod" responses over to the "Archives" it includes the empty rows. This is because in the "Approved" column the checkbox is technically false, which means it's included. How would I go about changing the code to only transfer rows that have data in the A column?

Thanks!

Upvotes: 1

Views: 221

Answers (1)

Tanaike
Tanaike

Reputation: 201368

I believe your goal as follows.

  • You want to copy the values from Prod to Archives when the value of column "A" in Prod is not empty.

Modification points:

  • In your script, all values of var data = s.getRange('A2:F').getValues(); is copied to Archives. So in this case, it is required to check the column "A" of data.
  • When appendRow() is used in a loop, the process cost will be high. Ref

When above points are reflected to your script, it becomes as follows.

Modified script:

function archive() {
  // Get the information
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Prod');
  var a = ss.getSheetByName('Archives');
  var data = s.getRange('A2:F').getValues();
  
  // Archive the results <--- I modified this part.
  var copyValues = data.filter(([a]) => a.toString() != "");
  a.getRange(a.getLastRow() + 1, 1, copyValues.length, copyValues[0].length).setValues(copyValues);
  
  // Clear the results
  s.getRange('A2:E').clear();
  s.getRange('F2:F').uncheck();
}

References:

Upvotes: 2

Related Questions