CODEWITHSUNDEEP
google-apps-script
user13584188
user13584188

Reputation: 13

How to copy data from source range and paste only rows with content to a separate target workbook sheet, sorted by date values

Seeking a script that will copy data from source sheet range B4:C and paste to a separate target workbook columns H4:I only rows with content in one or both cells (ie. discarding row 7 of the top table below), sorted by source column C date values latest to earliest, as demonstrated in the tables below:

enter image description here

Below is best I can make it work, though it doesn't omit empty rows, sort by date, and only evaluates a static source range. And it takes kind of a long time to process (~2 minutes, but it's not that big of a deal), so I'm hoping a proper script as suggested by the Community might improve that aspect as well:

function Import() {
  var cc = SpreadsheetApp.getActiveSheet();
  var ss = SpreadsheetApp.openById('sheetID').getSheetByName('tabname');
  var data = ss.getRange("B4:C8723").getValues();
  
  cc.getRange("H4:I").clearContent();
  cc.getRange("H4:I8723").setValues(data)
}

The desired script is, in effect, the following formula:

=query(importrange(sheet,tab!range),"select B,C where B is not null order by C desc")

Hope the request is clear; thanks for the help.

Upvotes: 0

Views: 59

Answers (2)

Tanaike
Tanaike

Reputation: 201573

I believe your goal is as follows.

  • You want to convert the formula of =query(importrange(sheet,tab!range),"select B,C where B is not null order by C desc") to Google Apps Script.

In this case, how about the following modification?

From:

cc.getRange("H4:I").clearContent();
cc.getRange("H4:I8723").setValues(data)

To:

var values = data.filter(([b]) => b != "").sort(([,c1], [,c2]) => c1 > c2 ? -1 : 1);
cc.getRange("H4:I").clearContent();
cc.getRange("H4:I" + (values.length + 3)).setValues(values);
  • In this modification, the filtered values are sorted and put them to the destination sheet.

References:

Upvotes: 1

Cooper
Cooper

Reputation: 64100

function Import() {
  var dss = SpreadsheetApp.getActive();
  const dsh = dss.getActiveSheet();
  var sss = SpreadsheetApp.openById('sheetID');
  const ssh = sss.getSheetByName('tabname');
  var vs = ssh.getRange(4,2,ssh.getLastRow() -3,2).getValues().filter(r => r[0] && r[1]);
  dsh.getRange(4,8,dsh.getLastRow() - 3, 2).clearContent();
  dsh.getRange(4,8,vs.length,vs[0].length).setValues(vs)
}

Upvotes: 0

Related Questions