How to copy and paste data from another spreadsheet with condition and specific range with Apps Scri-CodePudding

I want to copy and paste the values from another spreadsheet with a condition on source spreadsheet if column D is not null and Column E is null, then copy and paste the data. I have a lot of data about 12k rows and 50 columns. I already made my own code, but it got some timeout error while accessing the target sheet because it takes too long to paste the data/ values.

function myfunction() {
  var source = SpreadsheetApp.openById('').getSheetByName('');
  var datas = source.getRange(2, 1, source.getLastRow() - 1, 6).getValues(); //get the values until column 6
  var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('');

  var arr = [];
  for (let i in datas) {
    if (datas[i][3] != '' && datas[i][4] == '') {
     arr.push(datas[i]);
    }
  }
  target.getRange("A2:G").clearContent();
  target.getRange(2, 1, arr.length, arr[0].length).setValues(arr);
  target.getRange(2, 1, target.getLastRow() - 1, target.getLastColumn()).sort({ column: 5, ascending: false });
}

CodePudding user response：

Try this instead:

function myfunction() {
  var source = SpreadsheetApp.openById('').getSheetByName('');
  var datas = source.getRange(2, 1, source.getLastRow() - 1, 6).getValues(); //get the values until column 6
  var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('');

  var arr = datas.filter(e=>e[3]&&!e[4]).sort((a,b)=>b[4]-a[4]);
  var range = target.getRange("A2:G");
  range.clearContent();
  range.offset(0,0,arr.length,arr[0].length).setValues(arr);
}

CodePudding user response：

Try it this way:

function myfunction() {
  var source = SpreadsheetApp.openById('').getSheetByName('');
  var datas = source.getRange(2, 1, source.getLastRow() - 1, 6).getValues(); 
  var target = SpreadsheetApp.getActive().getSheetByName('');
  var arr = datas.map((r,i) => {
    if(r[3] != '' && r[4] == '') {
      return r;
    }
  });
  target.getRange("A2:G"   target.getLastRow()).clearContent();
  target.getRange(2, 1, arr.length, arr[0].length).setValues(arr);
  target.getRange(2, 1, target.getLastRow() - 1, target.getLastColumn()).sort({ column: 5, ascending: false });
}

CodePudding user response：

Class SpreadsheetApp methods are very slow. Try using the Advanced Sheets Service or the Sheets API (you will need to use Class UrlFetchApp).