I found the script below on the web to clear values of a Google Sheet range. Can anyone offer suggestions on what additional might be required to get the script to work?
Nothing appears to happen in the referenced Google Sheet (which I own and is open in a Chrome tab). When I run it in the script editor, the result shows "undefined". Is providing the sheet name insufficient and/or is the url also required?
The code visible in the panel defines a function but doesn’t call it. (Abstraction wthout application).
You could either:
clearRange() under the function definition, or(function clearRange() {
...
})()
Complexpoint,
Thank you for your response!
After making the recommended modification shown above, the error below is returned in the script editor:
Does "SpreadsheetApp" need to be defined in some way?
Well, that’s why I wondered if there was other code hidden by the panel, or whether this function definition was, perhaps, cut from a fuller context.
So yes – unless the name SpreadsheetApp is already bound to some object in the global context, it won’t be possible to evaluate the whole expression.
Help, I’ve searched the web and can’t find any solution to the Javascript SpreadsheetApp issue! Is there an alternative approach to clearing a cell range of a Google Sheet?
Any suggestions would be appreciated.
I've only just noticed where you are trying to evaluate that code – It won't ever be possible to do it in the JSContext (Javascript interpreter) of JavaScript for Automation – it will only run in the JavaScript interpreter of a web browser.
i.e. your first screenshot is feasible, if you can get a reference to the SpreadsheetApp object in a Chrome-embedded JS interpreter, but what happens in Script Editor's embedded JS interpreter is not relevant or illuminating – that is a completely different and unrelated evaluation context – one which will always fail if you try to reference browser interface objects in it. It doesn't have any interface to a browser environment at all.
The 'script editor' referred to here
https://developers.google.com/apps-script/quickstart/macros
is, of course Tools > Script editor in Google Sheet
If you want to see the object names in the evaluation space available to an Execute Javascript in Google Chrome action, you could run this, displaying the results in a window:
Object.getOwnPropertyNames(this).join('\n')
The JS evaluation space of Google Sheet looks private to the Sheet web app to me – out of reach from a KM action.
Possibly, if you were to set up some Google Sheet macro code in Google Sheets > Tools > Script editor ... you might be able to use Keyboard Maestro to place clicks in Google Sheet to choose items in a script-generated custom menu in Google Sheets, but otherwise, I think that JS execution may not be the route you are looking for.
ComplexPoint,
WOW! It should be obvious that I clearly know nothing about Javascript and I don’t understand most of what you’ve provided other than nothing that I’ve tried works or is possible!
Before your message, I did try entering a few variations of code as a project in the Google Sheet > Tools > Script Editor… For example:
(function clearSteelReport() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange(“D5:D22”);
range.clearContent();
})()
All attempts resulted in a failure to clear the targeted range due to an error (e.g. Script function not found: clearSteelReport) which maybe is what you’re also saying.
I’ll run the code you’ve offered and see what I get for object names and try to interpret what I can.
I realize this really isn’t a KM problem which is why I was originally reluctant to post for assistance, but is there no way to access and modify a Google Chrome Sheet comparable to the way I can modify or get data from an MS Excel spreadsheet through KM using AppleScript?
Thanks for your attention and consideration
The TL;DR is that it’s an ‘embedded’ scripting language and that what you can run depends on what it’s embedded in, and what programmable interfaces that context supplies.
To beat an old drum it might be possible to craft a javascript: URL - otherwise known as a bookmarklet - and inject that into the browser search bar.
I have no idea what these last posts are trying to convey other than what I originally posted does/will not work and particularly in the context of what I was trying (.i.e in KM)! I got the list of object names using ComplexPoint’s statement [Object.getOwnPropertyNames(this).join(’\n’)] and couldn’t make “heads or tails” or the point of them. I don’t know Javascript!
If my goal is not possible as posted, please offer an alternative approach supported with an example (bookmarklet??) such that I might try to understand how to make it work towards my goal. Drum and horse thoroughly beaten!
Thank you
Returning to your original question, the first step is to get the function working in Google Sheets.
I haven’t personally used Sheets, but the instructions for installing and using that kind of macro are here:
https://developers.google.com/apps-script/quickstart/macros
https://developers.google.com/apps-script/guides/menus
I’m not sure, however, that Keyboard Maestro, which is developed to control macOS apps rather than web apps, will turn out to be the right tool for this. It has no access to the internal JavaScript of Sheets.
