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?
Yes why not but with the first row with id, CodeBarre, Désignation, Type, Lot and quantité, see the picture.
You can just add that (copy and paste it from the first sheet to the second/third sheet) before copying any data over.
I will re-write the function again.....
function copyRow() {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName('Sheet1');
var sh2 = ss.getSheetByName('Sheet2');
var sh3 = ss.getSheetByName('Sheet3');
var fn = 'COPYROW';
var rg = sh.getDataRange().getValues();
var rg2 = sh2.getDataRange().getValues();
var rg3 = sh3.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 ids2 = [];
for ( var j = 0; j < rg2.length; j++ ) {
ids2.push(rg2[j][0])
}
var ids3 = [];
for ( var k = 0; k < rg3.length; k++ ) {
ids3.push(rg3[k][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' ) {
// if the selected row's id value is not in the second sheet
// then append the row to the second sheet
if (!ids2.includes(rg[i][0])) {
var row = rg[i];
sh2.appendRow(row);
}
}
else if ( rg[i][3] == 'Exercise' ) {
// if the selected row's id value is not in the third sheet
// then append the row to the third sheet
if (!ids3.includes(rg[i][0])) {
var row = rg[i];
sh3.appendRow(row);
}
}
}
}
}
Sheet1
Sheet2
Sheet3
Note: you will need to add the third sheet to your parameters
If you want to colorise your rows/cells, i suggest you use conditional formatting on the google sheet for that. Much easier than coding it in.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.