Good morning,
I would like to copy rows according to a criteria in sheet1 to sheet2. I would like to do this to make a backup according to my criterion and that people modify sheet2 (in particular the quantities of the products).
Thanks to you TIMAI2 for the CRUDII, I will try to use it with my data.
Ok, thank you but this solution have not condition. But i found this script but not works with me.
My sheet are here : BDLabADN - Google Sheets
function copyIf() {
var feuille = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Produits");
var archive = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CopiData");
if (feuille.getLastRow()==1){
SpreadsheetApp.getActive().toast('No data!', 'End of script 🗃️')
return
}
var data = feuille.getRange(2,1,feuille.getLastRow()-1,feuille.getLastColumn()).getValues()
var archiveData = []
var lignes = []
var ligne = 1
var col = 4 // Mission
try {
data.forEach(function (row) {
if (row[col]=="UGIVC") {
archiveData.push(row)
lignes.push(ligne)
}
})
archive.getRange(archive.getLastRow() + 1, 1, archiveData.length, archiveData[0].length).setValues(archiveData)
// lignes.reverse().forEach(x => feuille.deleteRow(x));
SpreadsheetApp.getActive().toast('Rows '+lignes.flat()+' hab been archived !', 'End of script 🗃️')
} catch (e) {
SpreadsheetApp.getActive().toast('No data to be archived!', 'End of script 🗃️')
}
}
Why my script doesn't work?
What conditions are required in the data for the row to be copied?
Can it be copied at the same time the data is first uploaded ?
The conditions are in collum D "Mission", the people choose Mission by a button in app inventor and the script copy data in news sheet (CopiData).
After they can update data if they want. the copy is for the peaople don't change the data in produit.
OK, from what you say:
The fourth item in the data must contain the word "Mission" for the row to be copied.
When the data is first uploaded to the first sheet, if "Mission" is present in the fourth item, then copy the row to the second sheet.
Is this correct ? If so you could do this (if you are using my CRUDII google apps script):
function doGet(e) {
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var sh2 = ss.getSheetByName(e.parameter.SH2); //ADD
var fn = e.parameter.FN;
var rg = sh.getDataRange().getValues();
// Create/Add new record, using comma separated values
if ( fn == 'CREATE' ) {
var data = e.parameter.DATA.split(',');
sh.appendRow(data);
if (data[3] == "Mission") { /*ADD*/
sh2.appendRow(data); /*ADD*/
} /*ADD*/
return ContentService.createTextOutput("New record created");
}
....
}
Good morning,
Thanks for your help TIMAI2.
I'm going to be more specific what I want is for the user to choose the mission from a list: UGIVC or Corpse or Exercise then automatically Google will copy the products by looking in the "Mission" column (the one in red) in a sheet called ProductCopy. For that AI2 will look at each line which will have the same mission that the user will have chosen and will copy it in the other sheet.
Then the user can modify or not the quantities of these products in the CopyProduct sheet.
You can go see my sheet by clicking on the link of my first post.
I tried your script but it only marks if I create a product that's not what I ask of it.
I have written this blind without testing:
function doGet(e) {
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var sh2 = ss.getSheetByName(e.parameter.SH2); //ADD
var fn = e.parameter.FN;
var rg = sh.getDataRange().getValues();
// Create/Add new record, using comma separated values
if ( fn == 'CREATE' ) {
var data = e.parameter.DATA.split(',');
sh.appendRow(data);
return ContentService.createTextOutput("New record created");
}
//Copy row if row item 4 contains "Mission"
else if (fn == 'COPYROW') {
for (var i = 0; i < rg.length; i++ ) {
if ( rg[i][3] == 'Mission' ) {
var row = rg[i];
sh2.clearContents();
sh2.appendRow(row);
}
}
....
}
In the app you simply send FN=COPYROW and SH2=secondSheet as parameters (along with ID and SH.
Any rows on the first sheet that have Mission as the fourth item (Column D) will be copied to the second sheet. Any previous content is removed from the second sheet.
I have guessed that you are now going to say, but I want to keep the data that is on the second sheet because it has been further edited. In which case you need to be clear as to what unique criteria is available in the row contents in order to be able to select it from the app.
It does not work. Nothing is copied to the other sheet.
Maybe in your code with the braces I didn't place them well...
If not, here are the blocks that I modified, I don't know if I did the right thing.
It's COPYROW. I also modify the drop-down menu with CopyRow in it
I guess I will have to build up an example and do some testing.....
I made a function with the same code, and it works as expected
function copyRow() {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName('Sheet1');
var sh2 = ss.getSheetByName('Sheet2');
var fn = 'COPYROW';
var rg = sh.getDataRange().getValues();
//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.clearContents();
sh2.appendRow(row);
}
}
}
}
Sheet1
Sheet2
Thanks TIMAI2
But i think my block is wrong. But i add block for read copyrow (look my message).
Fred
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
the same script but with multiple condition Mission,... after picking in list on AI2. How do i insert condition in script?