Google Sheets script is taking to long, can i use Cache to remedy that?

Question

I have a function that will input data typed in one sheet into another sheet (database) the sheet puts the data entered in the first blank row and the resets the box for the next input

However it takes the script around 11 seconds to run which is a bit to slow i have tried minimizing my api calls but it really dosent do anything so im guessing its because of the size of the database (Currently 25000+ Rows and 10 colums

i was thinking of trying to use cache to help me out but dont know if that would do anything for me since im not trying to get data from the database i only need the last row in the database and that will be a new one everytime anyways.

Hope my question is making sense im failry new at this :)

Here is the code im using

function CCBox() 
{
  var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
  var Indtastning = myGoogleSheet.getSheetByName("Indtastning");
  var Database = myGoogleSheet.getSheetByName("Database");
  var ui =SpreadsheetApp.getUi();
  var blankrow=Database.getLastRow()+1;

  if (ui.Button.OK)
  {
    //Henter Data og setter det ind i databasen
    Database.getRange(blankrow, 1).setValue(Indtastning.getRange("A3").getValue());
    Database.getRange(blankrow, 2).setValue(Indtastning.getRange("D3").getValue());
    Database.getRange(blankrow, 3).setValue(Indtastning.getRange("F3").getValue());
    Database.getRange(blankrow, 4).setValue(Indtastning.getRange("H3").getValue());
    Database.getRange(blankrow, 15).setValue(Indtastning.getRange("M3").getValue());

    Database.getRange(blankrow, 5).setValue(Indtastning.getRange("E10").getValue());
    Database.getRange(blankrow, 6).setValue(Indtastning.getRange("G10").getValue());
    Database.getRange(blankrow, 7).setValue(Indtastning.getRange("I10").getValue());
    Database.getRange(blankrow, 8).setValue(Indtastning.getRange("M10").getValue());
    Database.getRange(blankrow, 9).setValue(Indtastning.getRange("K10").getValue());

    Database.getRange(blankrow, 10).setValue(Indtastning.getRange("E18").getValue());
    Database.getRange(blankrow, 11).setValue(Indtastning.getRange("G18").getValue());
    Database.getRange(blankrow, 12).setValue(Indtastning.getRange("I18").getValue());
    Database.getRange(blankrow, 13).setValue(Indtastning.getRange("M18").getValue());
    Database.getRange(blankrow, 14).setValue(Indtastning.getRange("K18").getValue());

    //Rydder Felterne til ny indtastning
    Indtastning.getRange("A3:I5").clearContent();
    Indtastning.getRange("M3:O5").clearContent();
    Indtastning.getRange("E10:O12").clearContent();
    Indtastning.getRange("E18:O20").clearContent();

    myGoogleSheet.toast("Kvittering overført korrekt","",5);
    
  }


}

it is possible for me to send the sheet but i really would like to avoid it because i will have to purge all the data from it and that will take some time

Welcome to [so]. Please add a brief description of your search efforts for helpful content from this site as is suggested in [ask]. Once said, to reduce the execution time of a script the first thing to be done is to remove the unnecessary statements. In this case `var ui =SpreadsheetApp.getUi();` and `if (ui.Button.OK)`. — Rubén, Dec 17 '22 at 16:53

score 0 · Accepted Answer · answered Dec 16 '22 at 17:48

Try this:

function CCBox() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName("Indtastning");
  const vs = sh1.getRange("A1:M18").getValues()
  const sh2 = ss.getSheetByName("Database");
  const row = sh2.getLastRow() + 1;
  sh2.getRange(row, 1).setValue(vs[0][2]);
  sh2.getRange(row, 2).setValue(vs[3][2]);
  sh2.getRange(row, 3).setValue(vs[4][2]);
  sh2.getRange(row, 4).setValue(vs[7][2]());
  sh2.getRange(row, 15).setValue(vs[12][2]());
  sh2.getRange(row, 5).setValue(vs[4][9]());
  sh2.getRange(row, 6).setValue(vs[6][9]());
  sh2.getRange(row, 7).setValue(vs[8][9]());
  sh2.getRange(row, 8).setValue(vs[12][9]());
  sh2.getRange(row, 9).setValue(vs[10][9]());
  sh2.getRange(row, 10).setValue(vs[4][17]());
  sh2.getRange(row, 11).setValue(vs[5][17]());
  sh2.getRange(row, 12).setValue(vs[8][17]());
  sh2.getRange(row, 13).setValue(vs[12][17]());
  sh2.getRange(row, 14).setValue(vs[9][17]());
  sh1.getRangeList(["A3:I5","M3:O5","E10:O12","E18:O20"]).clearContent();
  ss.toast("Kvittering overført korrekt", "", 5);
}

This worked prefectly thank you so much – Deto Dec 19 '22 at 09:39 — Deto, Dec 19 '22 at 09:39

Google Sheets script is taking to long, can i use Cache to remedy that?

1 Answers1