Read/Determine Current Google Sheet Name

I use KM actions shown below as part of a macro on a daily basis each morning to automatically advance three Google Sheet tabs (representing calendar schedules for three different locations). The workbooks are shared with me as "View Only", but I'm wondering if the sheet names (shown below also) can be read using Javascript or Applescript to automatically move to the correct sheet if my view gets out of sync with the current calendar date.

Keyboard Maestro 8.2.1 Actions

Can Google Sheet names be read into a KM variable?

Hey Anthony,

Yes.

I took a quick look yesterday.

Execute a JavaScript in Safari.kmactions (857 B)

I don't see how to discern the active sheet though.

Nor can I see how to activate a sheet.

I imagine these are possible though.

-Chris

Chris,

Can you provide insight into the error obtained (see screenshot) by your macro?

I know almost nothing about JavaScript myself, but I can tell you that this kind of JavaScript won't work in Script Editor. It has to be run either in a browser's JavaScript console (which you can find in the "hidden" Develop menu in Safari or the View > Developer menu in Chrome) or in a KM "Execute a JavaScript in Safari/Chrome/Front Browser" action.

Hey Anthony,

You're trying to run a JavaScript for Safari as a JXA JavaScript.

You can't do that.

-Chris

Sorry, clearly I know even less! :laughing:

Not realizing there was a difference, I typically test scripts in the Script Editor for syntax or when undesired and unsuccessful results are yielded when run in KM. I'll need to study the difference between the types of scripts and context from which they are run. Any recommended references?

Once your advice was heeded, I've obtained the results Chris warned of initially (inability to recognize active sheet). Thanks!.

The JSC Javascript interpreter used by Safari is the same as that used by Script Editor,
so no syntax differences, but:

JavaScript is an embedded scripting language, and the libraries and objects exposed to it depend on what application it's embedded in.

To test this, try evaluating console.log(Object.keys(this)) in the Safari or Chrome consoles, and then try the same thing in Script Editor. You will see a different set of objects in the global namespace.

(Chrome has a different JS interpreter, but it's still ES6 JS, so the syntax is the same, but again, only a browser's global namespace contains objects like document and window)

In the case of the code which your screenshot shows, the document object is a model of a parsed HTML page in a browser.

As Script Editor is not a browser, it can have no idea what the name document might be bound to.

1 Like

Hey Anthony,

The script that I provided could not possibly discern the active sheet, because it's just collating the names and returning them.

That doesn't mean it's not possible to discover the active sheet β€” I just don't know how.

Okay – you can get it with AppleScript and System Events, and it's pretty fast.

Ah, hell – you're using Chrome – this only works in Safari...

----------------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2018/05/12 10:18
# dMod: 2018/05/12 10:18 
# Appl: Safari, System Events
# Task: Get name and position of Active Sheet of Google Sheets Spreadsheet.
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @Safari, @System_Events, @Name, @Position, @Active, @Sheet, @Google, @Sheets, @Spreadsheet
----------------------------------------------------------------

tell application "System Events"
   tell application process "Safari"
      tell (first window whose subrole is "AXStandardWindow")
         tell group 1 of group 1 of tab group 1 of splitter group 1
            tell scroll area 1
               tell UI element 1
                  tell group 1
                     tell group 24
                        tell group 1
                           tell group 1
                              tell toolbar 1
                                 set activeSheet to first pop up button whose attribute "AXDOMClassList"'s value contains "docs-sheet-active-tab"
                                 
                                 tell activeSheet
                                    set activeSheetName to its name
                                    set activeSheetButtonPosition to value of attribute "AXPosition"
                                    # its properties
                                    # its attributes
                                 end tell
                                 
                              end tell
                           end tell
                        end tell
                     end tell
                  end tell
               end tell
            end tell
         end tell
      end tell
   end tell
end tell

----------------------------------------------------------------

Given this you can get the position of a button and have Keyboard Maestro click it if necessary.

It's a finagle that works (so far), but there's no telling how fragile it will be over time.

-Chris

Hey Anthony,

Well, I still don't know how to detect the active sheet (in Chrome).

But – I have discovered that you can record a macro in Google Sheets to go to the specific sheet you want – and you can assign a keyboard shortcut to the macro.

Look in the Sheets Menu Bar under:

Tools > Macros > Record Macro

It's pretty straightforward, so you should be able to figure it out.

Maybe @ComplexPoint will weigh-in on detecting the active sheet.

-Chris

The Sheets model is not accessible through code injection or XPath etc but:

https://developers.google.com/sheets/api/guides/concepts

1 Like

I appreciate the support from you guys. I'm still digging around the Internet, but unfortunately, I don't have sufficient knowledge to be able to use the Google Sheets API and Chris' script to get the current tab into a KM variable.

Why is this macro unable to get the current tab for a Google Chrome spreadsheet? How can it be achieved?

Keyboard Maestro 8.2.1 β€œGet Google Current Sheet [Tab] Name” Macro

Get Google Current Sheet [Tab] Name.kmmacros (3.1 KB)

As far as I recall, the SpreadsheetApp object is only available inside a special Chrome plugin evaluation space, to which tools like KM can have no access.