0

I want to move a row from one spreadsheet (JobRequest) to another (Accepted) spreadsheet by clicking the checkbox automatically (no yes/no button) (located in col G). There are 7 columns of data to be moved. I want a dialog to popup at the time the box is checked too, to confirm that they have read that dialog box and accepted.

Ideally, I can log this and if they exit out of the dialog box the box unchecks, but I haven't gotten that far yet, so that's for another post.

For now, can you help be figure out where I went wrong for this checkbox to move to another sheet?

I keep getting the error

Error
TypeError: Cannot read property 'source' of undefined

I thought I already had it defined.

function onEdit(e){
  const src = e.source.getActiveSpreadsheet();
  const r = e.range;
    if(src.getName()!= 'JobRequests' || r.columnStart !=7 || r.rowStart == 1) return;
    const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Accepted');
    src.getRange(r.rowStart,1,1,7).moveTo(dest.getRange(dest.getLastRow()+1,1,1,7));
    src.deleteRow(r.rowStart);

}
function showFeedbackDialog() {
  var widget = HtmlService.createHtmlOutputFromFile("AcceptanceForm.html");
  widget.setWidth(400);
  widget.setHeight(500);
  SpreadsheetApp.getUi().showModalDialog(widget, "Send feedback");
}
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Cyndee Adkins
  • 28
  • 3
  • 1
    On this site please don't use "excel" as a common noun for spreadsheets. i.e. Excel uses VBA but Google Sheets uses Google Apps Script. – Rubén Aug 26 '22 at 02:47
  • I was unaware, and will do thank you! – Cyndee Adkins Aug 26 '22 at 17:05
  • Regarding the edit saying that you need assistance with Google Apps Script, please bear in mind that questions on this site should be specific. Considering this focus your questions in a specific issue / problem or concern. If you decide to focus on "fixing" the error message, please read https://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas ( having a question closed as duplicate is not a bad thing, it means that someone already answered what is being asked... ). – Rubén Aug 26 '22 at 17:52
  • If you decide to focus on something else, add a [mcve], add more details so others might reproduce the specific problem to be solved, i.e. add the specific steps that you are following to run the function, include some sample input data and the expected results. – Rubén Aug 26 '22 at 17:53

1 Answers1

0

Modification points:

  • When I saw your question and script, the reason of your issue might be due to getActiveSpreadsheet() of const src = e.source.getActiveSpreadsheet();. e.souece returns Spreadsheet object. In this case, getActiveSpreadsheet() cannot be used. Please use getActiveSheet().
  • In order to open a dialog, in this modification, I used SpreadsheetApp.getUi().alert().
  • In order to record a log, I used other sheet.
  • In order to uncheck the checkbox, uncheck() is used.

When these points are reflected in your script, how about the following modification?

Modified script:

function onEdit(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;
  if (src.getName() != 'JobRequests' || r.columnStart != 7 || r.rowStart == 1 || !r.isChecked()) return;
  const ui = SpreadsheetApp.getUi();
  const res = ui.alert("Move row?", ui.ButtonSet.YES_NO);
  if (res == ui.Button.NO) {
    r.uncheck();
    return;
  }
  const dest = e.source.getSheetByName('Accepted');
  src.getRange(r.rowStart, 1, 1, 7).moveTo(dest.getRange(dest.getLastRow() + 1, 1, 1, 7));
  src.deleteRow(r.rowStart);
  r.uncheck();
  const log = e.source.getSheetByName("log") || e.source.insertSheet("log");
  log.appendRow([new Date(), `row ${r.rowStart} was moved.`]);
}
  • When you use this script, please check the checkbox in column "G" of "JobRequests" sheet. By this, the script is run and you can see a dialog.
  • And, in this modification, a log is recorded to "log" sheet. But, in this case, please modify the log data in the above script for your actual situation.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thanks for the recommendations so quickly. I gave this a shot but it's still throwing this error" TypeError: Cannot read property 'source' of undefined – Cyndee Adkins Aug 26 '22 at 17:05
  • 1
    @CyndeeAdkins Please checkout https://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas – Rubén Aug 26 '22 at 17:44
  • @Cyndee Adkins Thank you for replying. And I apologize for the inconvenience and my poor English skill. About `I gave this a shot but it's still throwing this error" TypeError: Cannot read property 'source' of undefined`, I think that the reason of this is due to that you might directly run the script with the script editor. In this case, the event object is not given. By this, such error occurs. This has already been mentioned by Rubén's comment. So, in this case, please check the checkbox in column "G" of "JobRequests" sheet. By this, the script is run and you can see a dialog. – Tanaike Aug 26 '22 at 22:36
  • @Cyndee Adkins Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, I apologize. At that time, can I ask you about your current situation? I would like to study to solve your issues. – Tanaike Aug 31 '22 at 00:55