0

I've recorded a macro that highlights a text range, copies, inserts a row on a different sheet and pastes the data. I had it working fine and triggered by clicking a button but since this doesn't work on IOS (the reason I've moved from excel in the first place) I've seen you can use check boxes and the onEdit trigger. Below is what I've got so far but it doesn't work and I can't figure out why.

function onEdit(e) {
if (e.range.getA1Notation() === 'Input Screen!e3') {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A3:F3').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Data Table'), true);
spreadsheet.getRange('1:1').activate();
spreadsheet.getActiveSheet().insertRowsAfter(spreadsheet.getActiveRange().getLastRow(), 1);
spreadsheet.getActiveRange().offset(spreadsheet.getActiveRange().getNumRows(), 0, 1, 
spreadsheet.getActiveRange().getNumColumns()).activate();
spreadsheet.getRange('A2').activate();
spreadsheet.getRange('\'Input Screen\'!A3:F3').copyTo(spreadsheet.getActiveRange(), 
SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Input Screen'), true);
spreadsheet.getRange('E3').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
}}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Conor12x
  • 39
  • 1
  • 9
  • 2
    The is an apostrophe / single-quote character (`'`) missing on the second line. Maybe this is due a "copy-paste" error or it's the cause of the problem... please add the texual error messages and the execution logs. – Rubén Nov 07 '22 at 21:10
  • 1
    The typo is probably me copying and pasting from various other sources to try and get it to work. What I usually do is record the macro and then add whatever onEdit lines above. Sorry @Rubén I'm not sure where to get the execution errors but an answer below has answered my question. – Conor12x Nov 09 '22 at 16:59
  • 1
    Thanks for you reply. Regarding the execution errors, see https://developers.google.com/apps-script/guides/logging#exception_logging – Rubén Nov 09 '22 at 17:09

1 Answers1

1

This is how I would set it up. As for the rest of it I don't know because I don't like reading or writing code like macros do.

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Input Screen" && e.range.columnStart == 5 && e.range.rowStart == 3) {
    
  }
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Perfect that's work! Although I now have the issue that when I try have 4 copies of this, each referencing a row down, only the last one will work. Do you know how I could get this working? – Conor12x Nov 09 '22 at 16:57
  • In google apps script every function must have a unique name. Put all of the functionality in the same function separated by the appropriate logic – Cooper Nov 09 '22 at 17:24
  • I'm not sure what you mean there. I've tried having multiple in the same file both with and without additional function OnEdit lines and also having them in separate files with different names. The changes I'm making to the code is the trigger cell, copy range, and final cell selection. – Conor12x Nov 09 '22 at 17:39
  • Did you not say that you had 4 copies of the onEdit(e) function? If you do the they all need to have a different name which means that at least three of them will need to be installable. – Cooper Nov 09 '22 at 17:42
  • Yep, I had 4 copies each as their own separate macro and only the one lowest in the list worked. I tried combining them as one long macro and still only the last one worked. When combined my code covers lines 1-14 so I simply copies and pasted it again starting at line 15. I might be better starting this as a new question! – Conor12x Nov 09 '22 at 20:53
  • Keep in mind that all functions in a project must have a unique name. – Cooper Nov 09 '22 at 20:57
  • I'm not sure what you mean, the function name is onEdit(e) isn't it? I've tried changing that name and it prevents it from working. – Conor12x Nov 09 '22 at 21:15
  • 2
    If you have 4 functions named onEdit() then only one will work. – Cooper Nov 09 '22 at 21:18
  • Is there a way to have 4 functions that work as onEdit() in the same sheet performing different actions? – Conor12x Nov 09 '22 at 22:33
  • Please describe what the four function do and how they do it in the body of your question. Please attention to how they trigger the onEdit trigger because that must be unique to each function – Cooper Nov 09 '22 at 23:25
  • I've figured it out! It was a similar question to my other post [link]https://stackoverflow.com/questions/27030085/two-onedit-functions-not-working-together – Conor12x Nov 11 '22 at 09:06