transfer invoice info from the entry sheet to the data sheet by apps script in google sheets

Question

I wrote a code that transfer the info from the entry sheet to the data sheet, the problem is the invoice in the entry sheet may contain 100 rows to transfer, and when I try this way the program became slower when the user write more than 10 rows.

function Entry() {
  var SS=SpreadsheetApp.getActiveSpreadsheet();
  var EE=SS.getSheetByName("entry");
  var DataSheet=SS.getSheetByName("data");
  var Blankrow=DataSheet.getLastRow()+1;
  var Blankrow2=DataSheet.getLastRow()+2;
  var Blankrow3=DataSheet.getLastRow()+3;
  var Blankrow4=DataSheet.getLastRow()+4;
 
  var EE2=EE.getRange("D6").getValue();
  var EE3=EE.getRange("D7").getValue();
  var EE4=EE.getRange("D8").getValue();

  DataSheet.getRange(Blankrow,1).setValue(EE.getRange("D3").getValue());
  DataSheet.getRange(Blankrow,2).setValue(EE.getRange("F3").getValue());
  DataSheet.getRange(Blankrow,3).setValue(EE.getRange("D5").getValue());
  DataSheet.getRange(Blankrow,4).setValue(EE.getRange("E5").getValue());
  DataSheet.getRange(Blankrow,5).setValue(EE.getRange("G5").getValue());
  
  if(EE2!=""){
  DataSheet.getRange(Blankrow2,1).setValue(EE.getRange("D3").getValue());
  DataSheet.getRange(Blankrow2,2).setValue(EE.getRange("F3").getValue());
  DataSheet.getRange(Blankrow2,3).setValue(EE.getRange("D6").getValue());
  DataSheet.getRange(Blankrow2,4).setValue(EE.getRange("E6").getValue());
  DataSheet.getRange(Blankrow2,5).setValue(EE.getRange("G6").getValue());
  }
  if(EE3!=""){
  DataSheet.getRange(Blankrow3,1).setValue(EE.getRange("D3").getValue());
  DataSheet.getRange(Blankrow3,2).setValue(EE.getRange("F3").getValue());
  DataSheet.getRange(Blankrow3,3).setValue(EE.getRange("D7").getValue());
  DataSheet.getRange(Blankrow3,4).setValue(EE.getRange("E7").getValue());
  DataSheet.getRange(Blankrow3,5).setValue(EE.getRange("G7").getValue());
  }
if(EE4!=""){
  DataSheet.getRange(Blankrow4,1).setValue(EE.getRange("D3").getValue());
  DataSheet.getRange(Blankrow4,2).setValue(EE.getRange("F3").getValue());
  DataSheet.getRange(Blankrow4,3).setValue(EE.getRange("D8").getValue());
  DataSheet.getRange(Blankrow4,4).setValue(EE.getRange("E8").getValue());
  DataSheet.getRange(Blankrow4,5).setValue(EE.getRange("G8").getValue()); 
  }

  EE.getRange("D3").clearContent();
  EE.getRange("H3").clearContent();
  EE.getRange("J3").clearContent();
  EE.getRange("D5:D23").clearContent();
  EE.getRange("E5:E23").clearContent();
  EE.getRange("G5:G23").clearContent();
}

I want a way that make the code run faster.

Answer 1

I believe your goal is as follows.

• You want to reduce the process cost of your script.
• In your actual situation, there are 100 lines of the script like DataSheet.getRange(Blankrow,1).setValue(EE.getRange("D3").getValue());.
• The ranges of clearContent() is always constant of "D3", "H3", "J3", "D5:E23", "G5:G23".

In this case, how about the following modification?

Modified script:

function Entry2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = ss.getSheetByName("entry");
  var dstSheet = ss.getSheetByName("data");
  var srcLastRow = srcSheet.getLastRow();
  var [[d3, , f3], , ...srcValues] = srcSheet.getRange("D3:G" + srcLastRow).getValues();
  var temp = Array(5).fill(null);
  var dstValues = srcValues.map(([d, e, , g]) => d.toString() ? [d3, f3, d, e, g] : temp);
  if (dstValues.length == 0) return;
  dstSheet.getRange(dstSheet.getLastRow() + 1, 1, dstValues.length, dstValues[0].length).setValues(dstValues);
  srcSheet.getRangeList(["D3", "H3", "J3", "D5:E23", "G5:G23"]).clearContent();
}

• In your script,for example, when if(EE2!="") is false, the row of var Blankrow2=DataSheet.getLastRow()+2 is empty. I modified your script by reflecting on this.

Note:

• When I tested your showing script using a simple sample Spreadsheet, I confirmed that the result of my modified script was the same as your script. But, if that was not your expected result, can you provide a sample Spreadsheet? By this, I would like to confirm it.