2

I'm trying to cause a 3rd party function (CRYPTOFINANCE()) to update whenever I manually trigger an update. Currently it updates any time I clear the cel contents and hit Undo. So my thinking is to simply create a script that selects the cells I want to update, clears them, and hits undo.

Unfortunately I can't find any way to execute an undo via script. Mostly I find references to it not even existing as a function, however I found reference to model.undo() which sounds like what I need. I just can't figure out how to make it work.

My other thought was maybe there is a way to just select the Undo option from the Edit menu via script, but I have a feeling that's not possible either.

Can anyone suggest a way to Undo via script that doesn't involve using a secondary sheet to store the "undo" data?

| improve this question | |
  • At the moment I have solved my need by simply copying the selected range an pasting it over itself again, which results in the same thing I was after via undo. Still, if anyone knows how to script an Undo, it would be quite useful in other situations. – JVC Jan 7 '18 at 1:05
  • Is CRYPTOFINANCE() from an add-on, or is it a function you have created? – lltt Jan 7 '18 at 18:29
  • It's a 3rd party add-on – JVC Jan 8 '18 at 0:37
  • Do you use it by typing it as a function in sheet? – lltt Jan 8 '18 at 20:51
  • Yes just like any standard function – JVC Jan 8 '18 at 20:51
1

Google Apps Script hasn't the undo / redo commands.

NOTE: The Real Time API is not part of Google Apps Script and it's not included in the advanced services.

The alternative is to make the script to keep records of the changes made and if necessary use them to "undo" or "redo" the action.

| improve this answer | |
  • OK, that was basically the conclusion I had come to but I wasn't certain about that Real Time API. I've found a different approach that does what I needed for now anyway... thanks! – JVC Jan 7 '18 at 7:47
0

If your objective is to refresh the function CRYPTOFINANCE() which is entered into any given cell in the sheet, your script would need to do just that.

Using the getActiveRange() method, you can programmatically target the cells that you want to refresh. Simply select the cells you want to refresh in the spreadsheet to store them in range with the getActiveRange() method.

var ss= SpreadsheetApp.getSheetById(id);
var sheet = ss.getSheets()[0];
var range = sheet.getActiveRange();

From range you can get each individual cell with this:

var cell = range.getCell(row, column);
// respectively to the range selected with 1, 1 being the upperleftmost cell.

To refresh that value, use the setValue method as follows:

cell.setValue('CRYPTOFINANCE(value)');

When that String is entered into the sheet, it will automatically trigger CRYPTOFINANCE() and refresh.

| improve this answer | |
  • Interesting approach. I think for my purposes in this case, having my "refresh" in a custom menu item is much easier, I wouldn't want to have to enter a string into the sheet somewhere every time I wanted to refresh. And in my case most cells contain a cryptofinance function, but not all, so copy/pasting all the data works better for me. – JVC Jan 9 '18 at 9:23
  • You don't need to enter a string anywhere, you simply attach this syntax to a function that's built into a sidebar or dialog. – lltt Jan 9 '18 at 13:51
  • Hmmm I'm afraid I don't understand. Sidebar or dialog? Attaching syntax to a function...? I don't know what those mean. – JVC Jan 9 '18 at 20:25
  • Use GAS + JS and HTML to create a sidebar add-on that contains a button that refreshes the selected cells. – lltt Jan 9 '18 at 21:00
  • Ah ok, that's all beyond me at this time and I don't really want to go down the rabbit hole for this. I appreciate the suggestion anyway. – JVC Jan 10 '18 at 6:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.