Script error

[Bobby Clowers]

Could anyone tell me why this this makes my script fail?

If I uncomment the  highlighted sections the first functions runs fine but the next one fails with this error in pic2.  These are onEdit functions.

[Andrew Roberts]

As the error says, you value you are writing to that cell contravenes the data validation you have applied.

[Bobby Clowers]

Very new to this,  but it's in a separate function and it's affecting a different cell.  They all are supposed to be adding these list to different cells if any cell in column 2 / b  is edited so why does that one add cause it to fail on the next function.  I can copy the whole script if it would help.

[Andrew Roberts]

If you are able to share a sanitised copy of the sheet and the script we could get a better idea of the issue.

[Bobby Clowers]

Here is a link

https://docs.google.com/spreadsheets/d/1KAL4D5o7jPfZtyMc2U_UNAMshzdVDWzl5jMg4PG82d8/edit?usp=sharing

[Michael O'Shaughnessy]

Well.... I am not getting an error.  HOWEVER, on line 51 you are setting the dropdown to a value that is not on your list.  Your valide values are coming from =List!$A$2:$A$30 and "Please Select Location" is not on the list....

I would start there.

--
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/4d8bdacf-2a0a-4d36-b34b-8945b9b65b9an%40googlegroups.com.

[Bobby Clowers]

Did you uncomment the lines back out so they are active?

[Bobby Clowers]

I edited the script and added the setvalue  "Please Select Location" to the top of the listed range and it now seems to work.  Thanks for all of yall input.

[Bobby Clowers]

I found another issue I hope yall could help with.  I recorded a video just to show you also. Here is the Sheet again. I tried to attach video but wouln't let me do that either so here is LINK

I had longer message but wouldn't let me post but  if I copy and paste info it seems to run script against all columns and if I highlight and delete more that just column b it does the same thing.

[Keith Wallace]

Could you share your underlying code?

I actually have the same or similar problem - one I’ve been trying to solve at low-moderate intensity for a while, but off and on. Knowing that it’s not only my problem encourages me to get to the bottom of it.

My thoughts on it at the moment (though not knowing your underlying code) — is that curtailing the on-edit functions authority when it sees that the edit is a copy-paste or is larger than one cell might alleviate the issue. To my eyes, it seems like the function runs repeatedly for each cell in the copy-paste range.

Here's a sample of my own code that produces similar results (when copy-pasting a range of more than one cell into an on-edit range):

function entryAddDate(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var s = e.source.getActiveSheet();

 

  if (s.getName() == "Schools") {

    var r = e.range;

   

    if (r.getColumn() == 6 && r.getRow() > 1) {

      var numRows = r.getNumRows();

      var dateEnteredColumn = r.offset(0, -4, numRows, 1);

      var dateIntendedColumn = r.offset(0, -3, numRows, 1);

I won’t have time to work on this directly until tomorrow, but hopefully it’s enough for a thought starter.

- KW

On Thu, Apr 25, 2024 at 09:20 Bobby Clowers <bobby....@pcs.k12.va.us> wrote:

I found another issue I hope yall could help with.  I recorded a video just to show you also. Here is the Sheet again. I tried to attach video but wouln't let me do that either so here is LINK

I had longer message but wouldn't let me post but  if I copy and paste info it seems to run script against all columns and if I highlight and delete more that just column b it does the same thing.

--

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/5de01d78-750c-4dc5-9183-b6250ebda8ebn%40googlegroups.com.

[Bobby Clowers]

Will it not let you see the script?  the file has edit access to anyone with a link.

[Keith Wallace]

My apologies, you're right. It's there. Taking a look.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/bfc3293c-15c8-437c-8620-c85cc68b6ea1n%40googlegroups.com.

[Bobby Clowers]

Any ideas yet on this?

[olivercre...@gmail.com]

Hi there,

I believe a solution might be to call the getCell method after the offset method, like so:

r.offset(0, 4).getCell(1, 1).setDataValidation(rule)

This will limit the range that you're setting the validation for to just the first cell of the offset range. This change should produce the expected behavior, but please let me know if I misunderstood. Here is a copy of your sheet that uses the getCell method: Link

The offset method appears to offset the entire range, which would explain why your result appears the way it does. For example, if we delete values from range "B2:D2", the "r" property on the onEdit event object will reference "B2:D2". When we use r.offset(0,4), the new range referenced by the offset is going to be "F2:H2" (four columns to the right of the original range). When we call setDataValidation on that offset range, we end up setting the validation rule for each cell in the range, not just "F2". By using the getCell method we can restrict the range to the first cell in our offset range.

Thanks,

Oliver

[Bobby Clowers]

OMG You are awesome Thank you very much.  Thanks for kinda explaining also. This seems to work flawlessly but just a other question and I may be wanting it to do more that its capable of.  Let just say I have 3 users and I have their first and last names copied and I paste in b10 which will be b10:d12 with the 3 users. It seems to only populate the rules for row 10 and the others are blank. Is there a fix for this also? The same thing happens if I delete the info from multiple than have already been populated.

[olivercre...@gmail.com]

Hello,

To make the script affect multiple rows, we can adjust the approach. The getCell method only gets the first cell in the first row of range "r", which is why it's only populating rules for row 10 (the first row in your example). Instead, we should get the entire first column of "r" so the script will populate rules for all the rows.

We can use the following code to do this:

    const firstColumnOfRange = src.getRange(
      r.getRow(),
      r.getColumn(),
      r.getNumRows(),
      1
    )
    firstColumnOfRange.offset(0, 4).setDataValidation(rule)

In this code, firstColumnOfRange represents the entire first column of range "r". Using your example where "r" is "B10:D12", firstColumnOfRange will be range "B10:B12". The offset(0, 4) part of the code then applies the validation rule to all the cells in range "F10:F12" (the range 4 columns to the right of "B10:B12").

To help reduce repetition, I made the following function:

    function getOffsetColumn(src, r, columnOffset){
      return src.getRange(
        r.getRow(),
        r.getColumn(),
        r.getNumRows(),
        1
      ).offset(0, columnOffset)
    }

One way you can use it in your code is like so:

    getOffsetColumn(src, r, 4).setDataValidation(rule)

This works the same way as the above. It applies the data validation rule to each cell in column F for the rows in the affected range.

Here is a copy of the spreadsheet that uses this updated approach: LINK.

Thanks,

Oliver

[Bobby Clowers]

Thank you very much. Again this works as expected also.