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>
1 Answer 1
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);
}
Explore related questions
See similar questions with these tags.