Convert onEdit function into a regular function - Apps Script

0
votes

I am using this function. It works well as an onEdit function. But, sometimes when I check more than one boxes quickly, the onEdit functions instantly stops the running execution in the middle and runs the new execution. Causing incomplete transfer of data to Sheet 2.

So, how can I convert this into a regular function? So, it completely processes/ transfers all the rows present in sheet 1 to sheet 2.

Dummy sheet link: https://docs.google.com/spreadsheets/d/1EvmWZsnAJK-htYNiPkgYK5oowl8uzOoz1CILvADC93I/edit?usp=sharing

function onEdit(e) {
  const sh=e.range.getSheet();
  if(sh.getName()=="Sheet 1" && e.range.columnStart==1 && e.value=="TRUE") {
    const tsh=e.source.getSheetByName('Sheet 2');
    const nr=tsh.getLastRow()+1;
    sh.getRange(e.range.rowStart,2,1,1).copyTo(tsh.getRange(nr,2,1,1));
    sh.getRange(e.range.rowStart,4,1,1).copyTo(tsh.getRange(nr,4,1,1));
    sh.getRange('E2:E').copyTo(tsh.getRange(nr,11,1,1));

 
  }
}
1
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
what is the expected output? can you add it in your sheet? – soMario
@Marios the output will be same to what I have in Sheet 2. This OnEdit function is just a chunk of the actual onEdit function which copies values from multiple columns to sheet 2. So, there is a large amount of data that is being copied over to sheet 2 which takes few seconds. So, I have to wait until the data is completely copied over otherwise, if I check another box during that execution. The execution in process instantly stops. And, runs the new execution (starts copying data of the newly checked row). So, I basically end up with incomplete copied data for the previous execution. – Jack871
@Marios I think that issue is due to the native nature of the onEdit function. Therefore, I am trying to convert this into a regular function. So, I get uninterrupted execution without any loss of data. I hope I am explaining it right. – Jack871

1 Answers

1
votes

Explanation:

The idea is to filter on checkboxes that are ticked (true) and then populate the correct arrays to be pasted to the target sheet.

Solution:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName('Sheet 1');
  const sh2 = ss.getSheetByName('Sheet 2');
  const sizes = sh1.getRange('E2:E5').getValues().flat();
  const vals1 = sh1.getRange('A2:D'+sh1.getLastRow()).getValues();
  const values = vals1.filter(r=>r[0]==true).map(([,b,c,d])=>[b,c,d]);
  const emptyAr = [...new Array(3)].map(elem => new Array(3));
  const valuesAr = values.flatMap(r=>[r,...emptyAr]);
  const sizesAr = new Array(values.length).fill(sizes).flat().map(c=>[c]);
  const lrow = sh2.getLastRow();
  sh2.getRange(lrow+1,2,valuesAr.length,valuesAr[0].length).setValues(valuesAr);
  sh2.getRange(lrow+1,11,sizesAr.length,sizesAr[0].length).setValues(sizesAr);
}