Are you sending the parameter for the second sheet?
No how to send the parameter? in CRUDIIAction ? but where i add second sheet
Do the same as the first sheet
I understand ?!? i have create a "FN" and "SH2" but in the block where i insert SH2??
In CRUDIIAction
add &SH2= and global sh2 below global sh.
You will need to create a new global variable sh2 and add the second sheet name
Ok thank you very much but i think if you have more than 1 row with "mission" the script rewrite in the first row. Because i have 2 rows with "Mission" and only the first row in second sheet is writing.
remove the sh2.clearContents() from the script. You will get duplicates in future additions.
Very good, i write clearContents before "if..." and it's very good.
Thank you again
1 Like
the same script but with multiple condition Mission,... after picking in list on AI2. How do i insert condition in script?
what do you mean by this?
If you want to find a row that has more than Mission in it then you will also need to send those parameters
In fact in my Products sheet I have products for Mission but also for exercise.
I would like that when I choose in a listpicker either mission or exercise the 2nd sheet is filled by the products which have one of these values.
if ( rg[i][3] == 'Mission' || 'Exercise') {
Your code is for mission and exercise ? but i want just Mission OR Exercise in the sheet.
In fact, I would like to put in my second sheet either the missions after having chosen in the mission drop-down menu (in green on the image) or the exercise products. I put 2 images of the result I want. I want the script to take the prosuits mission if I choose mission or exercise if I choose exercise.
Hoping to be clear enough because I have trouble expressing myself thank you very much for your help and to you TIMAI2.
after the script i have this in sheet2
We will have to check if item is already in second sheet, might get to this later today.
I test this script but it is no good
function doGet(e) {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName('Produits');
var sh2 = ss.getSheetByName('CopieProduit');
var fn = e.parameter.FN;
var rg = sh.getDataRange().getValues();
sh2.clearContents();
//Copy row if row item 4 contains "MISSION"
if (fn == 'COPYROW') {
for (var i = 0; i < rg.length; i++ ) {
if ( rg[i][3] == 'MISSION' ) {
var row = rg[i];
sh2.appendRow(row);
}
}
}
//Copy row if row item 4 contains "EXERCICE"
else if (fn == 'COPYROW2') {
for (var i = 0; i < rg.length; i++ ) {
if ( rg[i][3] == 'EXERCICE' ) {
var row = rg[i];
sh2.appendRow(row);
}
}
}
}
Looks like we can use ColA = id or ColB = barcode. These appear to be unique, so can test for their presence in the second sheet
Here is a revised function that I hope will do what you want.
It will find any rows in the first sheet that contain "Mission" or "Exercise", then test to see if the id values for those rows already exist in the second sheet. If they do not, the the rows are appended.
function copyRow() {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName('Sheet1');
var sh2 = ss.getSheetByName('Sheet2');
var fn = 'COPYROW';
var rg = sh.getDataRange().getValues();
var rg2 = sh2.getDataRange().getValues();
//Copy row if row item 4 contains "Mission"
if (fn == 'COPYROW') {
//get an array of all the id values in second sheet
var ids = [];
for ( var j = 0; j < rg2.length; j++ ) {
ids.push(rg2[j][0])
}
//test if a row in the first sheet contains Mission or Exercise
for ( var i = 0; i < rg.length; i++ ) {
if ( rg[i][3] == 'Mission' || rg[i][3] == 'Exercise' ) {
// if the selected row's id value is not in the second sheet
// then append the row to the second sheet
if (!ids.includes(rg[i][0])) {
var row = rg[i];
sh2.appendRow(row);
}
}
}
}
}
Sheet1
Sheet2
Hi,
i am confused but i don't want in sheet2 "Mission" and "exercise" but i want either mission products or exercise products.
Do you want to send them to separate sheets? Mission to sheet2 and Exercise to sheet3?
