Can someone suggest a means to access, modify or set a cell value of an open Google sheet file on the screen without using the Find Image action? I want to reset the date field when the file opens using KM.
I don’t see any way in Google Sheets to go to a specific cell.
But presuming you are in the spread sheet, you could do:
- Command-Left-Arrow (start of line).
- Repeat 10 times (however many times it is required to ensure you get to the top of the sheet)
- Command-Up Arrow
- Down Arrow
- Down Arrow
- Insert Text by Typing “%ICUDateTime%EEEE, dd-MMM-yy%”
There does not seem to be a command to go to the start of the spread sheet (well, there is but apparently it requires the Fn key, so I don’t know what it might be). Nor is there a command to go to the top of the column. But Command-Up Arrow goes in blocks (blocks of unempty cells), so depending on your spread sheet you may not need many of them.
Peter,
I was hoping for a means to find a specific Google Sheet cell if it is known. Could an AppleScript be used if the cell is known like accessing an Excel cell?
Thanks for responding and your assistance, as always.
No idea I’m afraid. Someone else might have an idea, but I’m not familiar with Google Sheets, and not much with web APIs either I’m afraid.
You can activate the help menu by typing Option-/ -- then type "go".
The first option will be "Go to range" (which doesn't seem to be accessible any other way)...
Selecting "Go to range" brings up a field where you can enter a cell...
I'm new to KM, so I'm not sure of the best of incorporating this into a macro.
Hope this helps.
-Dave
1 Like
dvandelay,
I definitely can use this! I was brute force deleting the contents of each cell individually to clear it and text replacement using the regex feature and move & click on image to change the one cell needing modification.
Keyboard Maestro 8.2 “Group” Action
Group.kmactions (45 KB)
Thank you.
1 Like
I know I'm a few years too late, but maybe this bit of info will help the next forum searcher: You could use Google's own macro recording and scripting features. E.g. I have a script that sets up conditional formatting in one column and adds another column containing a character-count formula. Notice the lines that get specific cells, and also a line that sets a cell value:
/** @OnlyCurrentDoc */
function highlightOver155AndAddCharsCol() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('B2').activate();
var conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule()
.setRanges([spreadsheet.getRange('B2')])
.whenCellNotEmpty()
.setBackground('#B7E1CD')
.build());
spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);
conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule()
.setRanges([spreadsheet.getRange('B2:B1000')])
.whenFormulaSatisfied('=len(B2)>155')
.setFontColor('#FF0000')
.build());
spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);
spreadsheet.getRange('E1').activate();
spreadsheet.getCurrentCell().setValue('chars');
spreadsheet.getRange('E2').activate();
spreadsheet.getCurrentCell().setFormula('=len(B2)');
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('E2:E100'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('E2:E100').activate();
};
I actually came to the forum this morning looking for a way into Sheets' menus, so this is a helpful thread. The PITA about Google scripts is that you can't easily copy them from one sheet to another; you have to rebuild them each time. Check out this article on the topic.
...and so I've just made a KM macro that takes care of rebuilding this script in any new spreadsheet and then running it. Fantastic.
I think this feature was probably added to Google Sheets after your question was posted and, perhaps, by now it's become common knowledge but, just in case... You can automatically select a specific cell or range of cells in Google Sheets, by adding it as a string at the end of the URL.
For example: https://docs.google.com/spreadsheets/d/[long ID]/edit#gid=957240945
If you want to open it with cell A7 selected, simply add "&range=A7" at the very end of the URL:
https://docs.google.com/spreadsheets/d/[long ID]/edit#gid=957240945&range=A7
1 Like
I figured out how to use googles API - its a lot better than manually opening sheets which I did for a long time...
Good luck to anyone who finds this