My goal: copydata on button from one sheet to other, only non-blank cells.
Basicly I created simple copy button, but it is counting everything in the spreadsheet. The whole spreadsheet is filled with formula like this one: =IF(B14="";"";Import!$D$2). My goal would be to only copy whole data that if the formula gives false = data and leave the blank formula alone.
My current code is like this:
function CopyData() {
const ss = SpreadsheetApp.getActive();
const sh1 = ss.getSheetByName('Auto'); // use your sheet name for sheet1
const sh2 = ss.getSheetByName('Test'); // use your sheet name for sheet2
const rg = sh1.getDataRange()//selects whole data
const drg = sh2.getRange(sh2.getLastRow() + 1 , 1);//Select destination range. You only have to specify the upper left corner of the range
rg.copyTo(drg, {contentsOnly: true});
}
I have found somewhere a code like this, but I'm to new to this to be able to aply it to my current code and I'm not sure if it would work.
rg.filter(function(row) {return row.filter(String).length > 0});
I also found a code that works as my goal would be, but the issue is that it is very slow it checks row after row and I sometimes have to copy for example 300 rows at once. That would simply take to long.
function CopyData(CopyData) {
var ss=SpreadsheetApp.getActive();
var sh1=ss.getSheetByName("Auto");
var sh2=ss.getSheetByName("Test");
var rg1=sh1.getRange("A2:M2000");
var vA=rg1.getValues();
for(var i=0;i<vA.length;i++){
if(vA[i][5]){
sh2.appendRow(vA[i]);
}
}
}
Is there a solution to my issue? If so what should my steps look like to fix it. Thank you in advance.
About I also found a code that works as my goal would be, but the issue is that it is very slow it checks row after row and I sometimes have to copy for example 300 rows at once., in your bottom script, appendRow is used in a loop. In this case, the process cost becomes high. Ref Author: me
From I also found a code that works as my goal would be, when you want to reduce the process cost of your bottom script, how about the following modification?
function CopyData() {
var ss = SpreadsheetApp.getActive();
var sh1 = ss.getSheetByName("Auto");
var sh2 = ss.getSheetByName("Test");
var rg1 = sh1.getRange("A2:M2000");
var vA = rg1.getValues().filter(r => r[5]);
sh2.getRange(sh2.getLastRow() + 1, 1, vA.length, vA[0].length).setValues(vA);
}