I have a sheet named Formularantworten 1 get data from a form, as following:
| Data (A) | Cliente(B) | Prodotto(C) | Unitá(D) | Q.tot(E) | Codice Cliente(F) | Cod.P(G) | Q.unitá(H) | Kg unit(I) |
|---|---|---|---|---|---|---|---|---|
| 22.02.22 | Mike | Beer | LT | 10 | 1001 | 94 | 1 | 1 |
then I have two more sheets: sheet named Clienti
| ID (A) | Customer Name(B) | Domain(C) | email(D) | Codice(E) | ||
|---|---|---|---|---|---|---|
| 1001 | Mike | mike.com | kkk@kkk.ccc | 1001 | 94 | 1 |
and sheet named Ordini
| Date(A) | Customer Name(B) | Domain(C) | SKU(D) | Quant(E) | Unit (F) | |
|---|---|---|---|---|---|---|
| 22.02.22 | Mike | mike.com | 94 | 10 | LT | 1 |
I have the following code:
//copia
function moveValuesOnly () {
//copia il nome ed il codice clienti da 'Formularantworten 1' a 'Clienti'
var ss = SpreadsheetApp.getActiveSpreadsheet ();
var source1 = ss.getRange ("Formularantworten 1!B2:B2");
var source2 = ss.getRange ("Formularantworten 1!F2:F2");
var destSheet = ss.getSheetByName("Clienti");
// Déterminer l'emplacement de la première ligne vide.
var destRange = destSheet.getRange(destSheet.getLastRow()+1,2);
source1.copyTo (destRange, {contentsOnly: true});
var destRange = destSheet.getRange(destSheet.getLastRow(),1);
source2.copyTo (destRange, {contentsOnly: true});
var destRange = destSheet.getRange(destSheet.getLastRow(),3);
source1.copyTo (destRange, {contentsOnly: true});
var destRange = destSheet.getRange(destSheet.getLastRow(),5);
source2.copyTo (destRange, {contentsOnly: true});
What I would like to do is to create a button and assign a script that copy and paste:
from sheet Formularantworten 1 columns F, B,F to sheet Clienti columns A,B,E being sure that if the value ID already exists in Clienti, then skip it
from sheet Formularantworten 1 columns A,B,G,E,D to sheet Ordini into column A,B,C,D,E,F
Hoping it is clear. Thank you in advance. Giacomo