Copy range to another spreadsheet created using a template

Question

I'm trying to create a script that will copy a range of data from one sheet to two other separate sheets when a user clicks "Yes" in the alert message. One of those two will be on a new spreadsheet created using a template. The data I want to copy is from a sheet called "data". The destination sheets are named "selected" and on the new spreadsheet, "csv". The new spreadsheet will be stored in a different folder. Found this [script][1] to be very helpful for me to get started.

The onEdit script is working fine for the "data" sheet and transfers the chosen row to the "selected" sheet when a user clicks the checkbox and the "Yes" button in the alert message.

  function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if(s.getName() == "data" && r.getColumn() == 16 && r.getValue() == "Yes") {
      var ui = SpreadsheetApp.getUi();
      var response = ui.alert('Are you sure you want to continue?', ui.ButtonSet.YES_NO);
      // Process the user's response.
      if (response == ui.Button.YES) {
          var row = r.getRow();
          var numColumns = s.getLastColumn();
          var targetSheet = ss.getSheetByName("selected");
          var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
          s.getRange(row, 1, 1, numColumns).moveTo(target);
          s.deleteRow(row);
      }
      Logger.log('The user clicked "Yes."');
        } 
        else if (response == ui.Button.NO) {
         clearAnswer();
      Logger.log('The user clicked "No" or the close button in the dialog\'s title bar.');
      } 
}

First issue I have is that the condition else if is not triggering the function "clearAnswer" when a user clicks NO in the alert message even though the function works when triggered in the script editor.

function clearAnswer() {
  var app = SpreadsheetApp;
  var clearSheet = app.getActiveSpreadsheet().getActiveSheet();
  clearSheet.getRange("P2:P").clearContent();
}

Second issue is I don't know where to begin with the script that will copy the chosen row from the "data" sheet to the "csv" sheet on a new spreadsheet which will be created using a template. I found this script so far.

// Pull spreadsheet template
  var ss = SpreadsheetApp.openById('TEMPLATEFILEID');
  var newSS = ss.copy("Copy of " + ss.getName());
// Move to original folder
  var targetFolder = DriveApp.getFileById('FOLDERFORNEWFILE');
  var newSSFile = DriveApp.getFileById(newSS.getId());
  originalFolder.addFile(newSSFile);
  DriveApp.getRootFolder().removeFile(newSSFile);
}*/

I would appreciate any help you can give me or I hope you can point me to the right direction so I can figure things out faster.

So, just to clarify, do you basically want to copy a range from one sheet to another when you mark YES on a cell right? And when you mark NO you want to clear all the cells of the origin sheet? What is the template sheet you want to use? Could you provide a sample sheet with no sensitive data so that your intentions and the way it is erratically behaving is clearer? Thanks ! :D — Mateo Randwolf
Edit your question to ask only one question, 1 problem. Ask separate problems in separate questions. 1. First issue I have is that the condition else if is not triggering the function "clearAnswer" Try view> executions> logs — TheMaster

mkpersonal mkpersonal · Accepted Answer · 2020-09-14T16:02:25

You else if is nested outside of the popup event. Try like this:

function onEdit(event) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var s = event.source.getActiveSheet();
      var r = event.source.getActiveRange();
      if(s.getName() == "data" && r.getColumn() == 16 && r.getValue() == "Yes") {
          var ui = SpreadsheetApp.getUi();
          var response = ui.alert('Are you sure you want to continue?', ui.ButtonSet.YES_NO);
          // Process the user's response.
          if (response == ui.Button.YES) {
              var row = r.getRow();
              var numColumns = s.getLastColumn();
              var targetSheet = ss.getSheetByName("selected");
              var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
              s.getRange(row, 1, 1, numColumns).moveTo(target);
              s.deleteRow(row);
Logger.log('The user clicked "Yes."');
          } else if (response == ui.Button.NO) {
             clearAnswer();
          Logger.log('The user clicked "No" or the close button in the dialog\'s title bar.');
            } 
       } 
    }
}

Copy range to another spreadsheet created using a template

1 Answers