2
\$\begingroup\$

I'm new in coding and I use google script in my spreadsheet to add a list of consecutive numbers automatically by giving the first number to add and the last one (with an arrival date and an option "A" or "B" for each number). I created a table to put all the informations with an html file. The code is "OK", it's working but it's too slow and I reached my limits in coding to improve the code so...is there somebody that can help me or give an hint on that ?

To make the message clearer, here is what I have and what I want at the end :

Before launching the code : enter image description here

After launching the code, completing the table (forget about the first line) : enter image description here

Final result with consecutive range of number added : enter image description here

Code.gs 

function onOpen(e) {
   SpreadsheetApp.getUi()
       .createMenu('Arrivage')
       .addItem('Add a new one', 'Arrivee')
       .addToUi();
 }

function Arrivee() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet().getSheetName();
  if (sheet.indexOf("Br") == 0) {
    SpreadsheetApp.getUi().alert("Vous devez sélectionner 
la feuille où ajouter l'arrivage et pas celle des colis !");
    return false 
  }else{  
  var html = HtmlService.createHtmlOutputFromFile('index.html')
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
  SpreadsheetApp.getUi()
       .showModalDialog(html, 'Remplissez les infos 
nécessaires pour le nouvel arrivage des ' + sheet);
  }
}

function itemAdd(form) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var feuille = ss.getActiveSheet();
  var nb_total = form.colis2 - form.colis1;
  for (i = 0; i <= nb_total; i++) {
    if (i < form.gare){
      var a_values = [[ +form.colis1 + i, form.ddc, "A" ]];
      feuille.getRange(feuille.getLastRow() + 1, 6, 1, 3).setValues(a_values);
    }else{
      var b_values = [[ +form.colis1 + i, form.ddc, "B" ]];
      feuille.getRange(feuille.getLastRow() + 1, 6, 1, 3).setValues(b_values);   
    }   
  }
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top" >
  </head>
  <form>
    Numéro arrivage :
    <input type="text" name="br">
    <br><br>
    Numéro colis 1 :
    <input type="text" name="colis1">
    <br><br>
    Numéro colis 2 :
    <input type="text" name="colis2">
    <br><br>
    Gare : 
    <input type="text" name="gare">
    <br><br>
    Date de création :
    <input type="text" name="ddc">
    <br><br>
     <input type="button" value="Ajouter"
        onclick="google.script.run
            .withSuccessHandler(google.script.host.close)
            .itemAdd(this.parentNode)" />
    </form>
</html>
\$\endgroup\$

1 Answer 1

Reset to default
3
\$\begingroup\$

I guess the answer could be useful to someone that begin like me in coding...Anyway the tip here was to create an array first (I named it table) instead of adding objects line by line and then update the sheet in a single operation with setValues. It makes running the code way much faster !

function itemAdd(form) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var feuille = ss.getActiveSheet();      
  var nb_total = form.colis2 - form.colis1;
  var table = [];
  for (i = 0; i <= nb_total; i++) {
    if (i < form.gare){
      table.push([+form.colis1 + i, form.ddc, "A"]);
      }else{
      table.push([+form.colis1 + i, form.ddc, "B"]);
      }
    }
  feuille.getRange(feuille.getLastRow() + 1, 6, table.length, 3).setValues(table);
  }
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.