Apps Script: force blur on editing cell to save its content

Asked 4/4, 2018 at 18:19 Answered 23/10, 2022 at 1:51

This is my scenario:

1 cell: A1 where you can input some text
1 image / drawing: when it is clicked a script function is called

The problem is that the script attempts to read A1 content, but if you was editing it, its content is not updated until you manually click in another cell (so if you click directly on the image right after editing A1, the script will read the previously stored A1 value)

The problem is that clicking in the image doesn't trigger a blur event on A1, so the content is not stored if you was editing it. I would like to know how to force blur / save / stop editing a cell using Apps Script, so I can be sure that the retrieved value is the one that you just entered. I've tried selecting another active range but it doesn't help.

Thank you.

Lobbyism answered 4/4, 2018 at 18:19 Comment(7)

Can you show where is your image ? How clicking on image doesn't remove cell A1 from focus – Heptachord 4/4, 2018 at 18:23

Pressing enter after clicking the image proves that the cell remains in focus. I've had this issue too Alvaro and not found any solution to it. – Indubitability 4/4, 2018 at 18:33

And what happens if in your script you force an active cell selection, then force pending writes with a flush(), and then proceed with your function? – Stodge 4/4, 2018 at 21:24

Thank you @Stodge but it doesn't work either, A1 doesn't lose its focus and the value is not stored so the script keeps reading its previous value. – Lobbyism 4/4, 2018 at 23:49

@AlvaroPrieto then I think the cell state is not available to the script and is enforced by the browser. I think you will need to use a different method than an image script, like a menu or edit-based trigger function – Stodge 5/4, 2018 at 2:2

I'm starting to think so too, thanks a lot! :-) – Lobbyism 5/4, 2018 at 9:41

I'm still searching for a "method"(or "action") to call, so it triggers "closing" of cell editing. – Standpoint 24/2, 2019 at 11:33

I use this workaround, in Google Sheets, to return the focus to a cell after clicking on a button that has a function associated with it (I use the programmatic opening of the sidebar and its immediate closure):

main.gs

function inserisciNuovaRiga() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  sheet.insertRowBefore(2);
  showSidebar();
  var range = sheet.getRange("A2");
  sheet.setActiveRange(range);
}

function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('sidebar');
  SpreadsheetApp.getUi().showSidebar(html);
}

sidebar.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body onload="google.script.host.close();">

  </body>
</html>

You can find more info here (use translator, the tutorial is in italian languange):

http://www.appsscript.it/tutorial/google-apps-script-eseguire-una-funzione-al-click-su-un-bottone-in-uno-spreadsheet-e-far-tornare-il-focus-su-un-cella/

In the case of the example of the article, after clicking on the button, the code inserts a new line and returns the focus to its first cell ready to write, but simply adapt it to your needs.

Garett answered 4/4, 2018 at 21:42 Comment(4)

Thanks Michele, you are very kind and I do appreciate it, however I think we are facing different scenarios. I tried your code and it doesn't fix my issue. The side panel opens and closes automatically, but A1 never loses its focus if I am editing it, so the script can't read the new value until I manually click in any other cell. In my case what I actually want is to save a cell value in case I am editing it, since if I click in an image/shape that acts as button, the editing cell doesn't loses it focus and thus is not saved and the script can't read the new value. – Lobbyism 4/4, 2018 at 23:35

If you change the destination cell after the sidebar, other than the edited one, the focus definitely loses it; i.e.: you edit cell A1 then click on the button that performs the function with showSidebar() and then setActiveRange in "A2". – Garett 5/4, 2018 at 6:46

Thank you Michele, but it doesn't work either. The closest I've got using what you said is that the editing cell remains in edition mode (even after showSidebar() and setActiveRange() ) and in the end the value that I just entered is stored in another cell (lets say A2). But the problem is that it is not stored until I manually click somewhere else or hit enter key. What I mean, is that no mater what I do with the sidebar or setActiveRange, the edition focus will never be over until I manually quit or accept it (esc / enter / mouse click). Thanks anyway! – Lobbyism 5/4, 2018 at 9:40

@AlvaroPrieto were you able to solve the issue? I am facing the same problem. – Flaw 18/2, 2021 at 16:16

Hopefully this helps someone facing this issue. I have had this problem with many sheets I build for clients. So many different users means at least some people don't understand that while editing a cell, you have not actually entered anything. So, I've had to build in different solutions. My favorite three are the following:

1 - I add a checkbox somewhere, and instruct people to check it before saving. The checks if the checkbox is checked, and throws an error message to the user if not. Once it is, the script runs and clears the checkbox.

2 - If the cell in question is empty before someone enters something, then I like this one: The script checks if the cell is blank. If it is, it throws an error message, but something fun like, "Looks like you were still editing. All fixed now! You can run the script again." Throwing the error makes them click "dismiss" or "ok" and this completes the edit on the cell they were editing, so on the next run, it will be fine.

3 - In some cases, it proves better to automate the script on edits. The onEdit script checks if the target cell was modified, and then runs only if it was. So, people don't need to click buttons anymore, it just runs when it's ready.

Another option you could consider is to have the button appear only when the cell has been edited. But that is a bit more "active" than I usually want a script to be.

Bottom line though, this is an unfortunate problem, but after years of writing scripts, I have never found a clean solution to just force the completion of the edited cell before running the script.

EDIT: I should've added, you can use the msgBox trick as part of the script as well. You can pop-up the msgBox and only continue the script after the user clicks "ok" or whatever button you add. You can just add some text there like, "Ready to run, click ok to continue" or something similar ;)

Hurtful answered 23/10, 2022 at 1:51 Comment(0)

Recommended topics

Hot tags