AppScript to Copy Row data is Column value is "Yes".

[Vikramarth Chand]

Hi, 

I need to create a backup of the row values in the even a certain column's text contains "Yes". I am using a script which copies the row data but there are two issues:

1. Copies formulas instead of values (copyValuesToRange doesn't seem to be working)

2. The column with "Yes" is autofilled based on other cells, so the onEdit script doesn't seem to consider this as an edit. If i manually enter "Yes" in the column, the row is copied, but it isn't copied if the column value is auto filled with Yes.

The script I am using is this :

 function onEdit(event) {

  // source data in sheet named Master Data

  // target sheet of move to named Backup

  // test column with yes/no is col 27 or AA

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var s = event.source.getActiveSheet();

  var r = event.source.getActiveRange();

  if(s.getName() == "Master Data" && r.getColumn() == 27 && r.getValue() == "Yes") {

    var row = r.getRow();

    var numColumns = s.getLastColumn();

    var targetSheet = ss.getSheetByName("Backup");

    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

    s.getRange(row, 1, 1, numColumns).copyTo(target);

  }

}

[Jchome tex]

this is not running and work on google sheet and found some error 

please share with correct code 

Thanks

[Michael O'Shaughnessy]

OK, my suggestion to overcome the "copying" part is to get the row values and then set those values in the other sheet. So where you have var row = r.getRow() change that to var rowValues = r.getRange("whatever the ranges is").getValues().  Then you can set those values with s.getRange("range dimensions of the values").setValues(rowValues).

For the on edit issues... my first question is this a "must happen right away" or could you set a trigger to run say every 10 minutes to get all the values and copy all those with a "Yes"?  If not imperative to run right away then I would use a trigger.  If it is a must then I suggest you do an "onEdit" of the cell that causes the "yes" column to be set.  You can then run your copy code.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/8b5c2a4a-8426-4317-8a7d-09534f7486bdn%40googlegroups.com.

[Jean-Luc Vanhulst]

Have you considered using

https://developers.google.com/apps-script/reference/spreadsheet/range#copyTo(Range)

Or do you have a specific reason NOT to use that?

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/eaaa5ae4-339d-4dab-a152-d7457092ab5eo%40googlegroups.com.

[Emerson Maia]

maybe that won't solve

function onEdit(event) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const s = event.source.getActiveSheet();
  const r = event.source.getActiveRange();

  if (s.getName() === "Master Data" && r.getColumn() === 27 && r.getValue() === "Yes") {
    const row = r.getRow();
    const numColumns = s.getLastColumn();
    const targetSheet = ss.getSheetByName("Backup");
    const targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  }
}

function createBackupTrigger() {
  const triggerName = 'backupTrigger';
  const triggers = ScriptApp.getProjectTriggers();
  const trigger = triggers.find(trigger => trigger.getHandlerFunction() === 'backupRows');

  if (!trigger) {
    ScriptApp.newTrigger('backupRows')
      .timeBased()
      .everyMinutes(5) // Adjust the interval as needed
      .create();
    console.log('Backup trigger created successfully.');
  } else {
    console.log('Backup trigger already exists.');
  }
}

function backupRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const s = ss.getSheetByName("Master Data");
  const data = s.getDataRange().getValues();
  const backupSheet = ss.getSheetByName("Backup");
  let targetRange = backupSheet.getRange(backupSheet.getLastRow() + 1, 1);

  data.forEach(row => {
    const columnValue = row[26]; // Column 27 (AA)
    if (columnValue === "Yes") {
      targetRange.setValues([row.slice()]);
      targetRange = targetRange.offset(1, 0);
    }
  });

  console.log('Backup completed.');
}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKTLZ53UbZ36W-2D0t5VEbVT2%2BTus5LN4brpfKVC0nLUYYRu4g%40mail.gmail.com.