I tried to build a script on my own, and did lots of research and tried a lot of code. The one from the question asked here seemed very promising, but I got an error which I could not solve.

The Problem: I want to copy all data from Sheet "Wunschliste" of a row to a sheet "Bestellt", on which in Colum A2:A in sheet "Wunschliste" is marked as "2 bestellt"( This is filled via a drop down. ).All data from Sheet "Wunschliste" which is marked as "2 bestellt" shall be deleted.

As this sheet is constantly filled with data, I want to use it as a archive, so all new entries which fullfil the criteria, need to be put under the last entry.

The sheet "Wusnchliste" got a header row and The date is filled in the column A : M. This is why I don't need to copy the whole sheet.

The code I tried:

function copyRowsWithCopyTo3() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Wunschliste');
  let sourceRange = sourceSheet.getRange("A2:L"   sourceSheet.getLastRow());
  let targetSheet = spreadSheet.getSheetByName('Bestellt');
  const values = sourceRange.getValues().flatMap(([, b, c, d, , , , , i, j, k, l]) => l == "2 bestellt" ? [[b, c, d, i, j, k]] : []);
  console.log(values)
  targetSheet.getRange(targetSheet.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
}

The Error I got:

TypeError: Cannot read property 'length' of undefined copyRowsWithCopyTo3 @ Code.gs:8

Can someone help me out here?

I read these articles, too (and modified them to test): Google Script: Copy row from one sheet to another depending on value

Copy and paste a row based on a cell's value

Google Script to Match cell value and copy Paste related data in different tab

CodePudding user response：

As far as I have tested it works OK, it only returns this error when you don't have any matching value in column L. You could wrap your last part of the function in an if statement:

    if(values.length>0){
        targetSheet.getRange(targetSheet.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
    }

You can also add at the end an option to delete the source rows:

function copyRowsWithCopyTo3() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Wunschliste');
  let sourceRange = sourceSheet.getRange("A2:L"   sourceSheet.getLastRow());
  let targetSheet = spreadSheet.getSheetByName('Bestellt');
  let origvalues = sourceRange.getValues()

  const values = origvalues.flatMap(([, b, c, d, , , , , i, j, k, l]) => l == "2 bestellt" ? [[b, c, d, i, j, k]] : []);
  console.log(values)
  if(values.length>0){
  targetSheet.getRange(targetSheet.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
  }
  for (i=origvalues.length-1;i>=0;i--){
    if (origvalues[i][11]=="2 bestellt"){sourceSheet.deleteRow(i 2)}
  }

}

CodePudding user response：

I updated the script as we where discussing and it worked! I also was able to add this scrip in the menu bar.

there is one thing I still struggle with:

After I put all the Data form the "Wunschliste" to the "Bestellt" Tab, i need to be able to do the same thing with the data in the "Bestellt" tab, so if I mark the status "Ist angekommen" I want to be able to trigger the script via the menu to copy and delete these Data to the Tab "Angekommen". Each of the scripts are working for its own. But when I am putting it together, it always says the scrip "function copyRowsWithCopyT05" cannot be found.

    function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('WUNSCHLISTE BEREINIGEN')
      .addItem('Bestellte Artikel übertragen', 'copyRowsWithCopyTo3')
      .addItem('Gelieferte Artikel archivieren', 'copyRowsWithCopyTo5')
      .addToUi();}

function copyRowsWithCopyTo3() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Wunschliste');
  let sourceRange = sourceSheet.getRange("A2:N"   sourceSheet.getLastRow());
  let targetSheet = spreadSheet.getSheetByName('Bestellt');
  let origvalues = sourceRange.getValues()
 
  const values = origvalues.flatMap(([a, b, c, d, e, f, g, h , i, j, k, l, m, n]) => n == "Ja - ist bestellt" ? [[b, c, d, e, f, g, h, i, j, k, l,m]] : []);
  console.log(values)
  if(values.length>0){
  targetSheet.getRange(targetSheet.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
  }
  for (i=origvalues.length-1;i>=0;i--){
    if (origvalues[i][13]=="Ja - ist bestellt"){sourceSheet.deleteRow(i 2)}
}
function copyRowsWithCopyTo5() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Bestellt');
  let sourceRange = sourceSheet.getRange("A2:M"   sourceSheet.getLastRow());
  let targetSheet = spreadSheet.getSheetByName('Angekommen');
  let origvalues = sourceRange.getValues()

    const values = origvalues.flatMap(([ a, b, c, d, e, f, g, h , i,j, k, l, m, n]) => l == "Ist angekommen" ? [[a, b, c, d, e, f, g, h, i, j]] : []);
  console.log(values)
  if(values.length>0){
  targetSheet.getRange(targetSheet.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
  }
  for (i=origvalues.length-1;i>=0;i--){
    if (origvalues[i][12]=="Ist angekommen"){sourceSheet.deleteRow(i 2)} 
  }
}
}