Get spreadsheet info

Get help with using AutoHotkey and its commands and hotkeys
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Get spreadsheet info

22 Feb 2018, 20:48

This should do it. It works on Internet Explorer. Cheers.

Code: Select all

q:: ;Internet Explorer - Google spreadsheet get text
WinGet, hWnd, ID, A
oWB := WBGet("ahk_id " hWnd)
;MsgBox, % oWB.document.getElementsByTagName("table").length

oTable := oWB.document.getElementsByTagName("table")[0]
oRows := oTable.rows
vOutput := ""
;MsgBox, % oRows.length

Loop % oRows.length
{
	oCells := oRows[A_Index-1].cells
	Loop, % oCells.length - 1
		vOutput .= oCells[A_Index-1].innerText "`t"
	vOutput .= oCells[oCells.length-1].innerText "`r`n"
}
MsgBox, % oTable.rows[2].cells[1].innerText
MsgBox, % oTable.rows[6].cells[2].innerText
oWB := oTable := oRows := oCells := ""
MsgBox, % Clipboard := vOutput
return

;e.g.
;Untitled spreadsheet
;https://docs.google.com/spreadsheets/d/1EbhS2g3AXw1_yqYmfM_iUzrv0y_sSTuXrvxFAvwe9SY/htmlview

;	A	B
;1
;2	g
;3
;4
;5
;6		o
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
Paul H 81
Posts: 9
Joined: 11 Dec 2019, 22:19

Re: Get spreadsheet info

29 Apr 2020, 12:56

Hi Jess! I've been looking all over for something like this - thank you! Quick question - I'm using your script and it's returning the name of all the sheets in the google sheet. I just want the data from all the cells copied to my clipboard. Any guess on what I'm doing wrong? I'm using the exact script that you posted.
User avatar
littlegandhi1199
Posts: 187
Joined: 29 Aug 2016, 23:58

Re: Get spreadsheet info

29 Apr 2020, 19:58

Paul H 81 wrote:
29 Apr 2020, 12:56
Hi Jess! I've been looking all over for something like this - thank you! Quick question - I'm using your script and it's returning the name of all the sheets in the google sheet. I just want the data from all the cells copied to my clipboard. Any guess on what I'm doing wrong? I'm using the exact script that you posted.
You need to access each cell programatically?
each row 's cells are usually separated by %A_Tab%
and each line by the normal "`n" linefeed character

Code: Select all

loop, parse, Clipboard, `n
{
loop, parse, A_Loopfield, %A_Tab%
{
cell := A_Loopfield
}
}
Script Backups on every Execution :mrgreen:
https://www.autohotkey.com/boards/viewtopic.php?f=6&t=75767&p=328155#p328155

Scrabble Solver 4-15 letter word outputs ( :crazy: # of inputs)
https://www.autohotkey.com/boards/viewtopic.php?f=19&t=34285
Paul H 81
Posts: 9
Joined: 11 Dec 2019, 22:19

Re: Get spreadsheet info

30 Apr 2020, 06:49

Thanks for responding! I just want to copy it so I can paste into Excel and use some basic COM on it. Essentially, my company uploads a new google sheet every morning with new data and we have to reference the sheet all day. I'm trying to create a script that navigates to the new google sheet, copies the data, opens Excel and pastes the new data so I can refer to it each day. At first, I was trying to use IE COM to simply download it but I could not figure out how make it click the file menu in googlesheets...so I'm using the following in Chrome. I case you can't tell, I'm a SUPER noob at this LOL. Thanks again!

Code: Select all

#!1::

Sleep, 100
Run, https docs.google.com /spreadsheets/d/1Jzk3LXp3mRFeywqaBGWf0SiO5D9NbWWjEUrx01a70o/edit#gid=0  Broken Link for safety
pageloaded := false
while(pageloaded = false)
{
	sleep 100
	ImageSearch, X, Y, 0, 0, A_ScreenWidth, A_ScreenHeight,C:\Users\Connections\Desktop\AHK Scripts\Tool Scripts\Saved Clips\Saved Clip 20200204090856.png
	if ErrorLevel = 0
	{
		pageloaded := true
	}
}

Sleep, 1000
Click, 188, 706
Sleep, 1000
Click, 20, 230
Sleep, 500
Send, ^a
Sleep, 1000
Send, ^c
Run, "C:\Users\Connections\Desktop\March _ April Daily Data  (3).xlsx"
Sleep, 200


Run,  "C:\Users\Connections\Desktop\Overdue March.xlsx"

Sleep, 1000
Send, ^v
return
User avatar
flyingDman
Posts: 881
Joined: 29 Sep 2013, 19:01

Re: Get spreadsheet info

30 Apr 2020, 09:52

Depending on the permissions for that specific Google Sheet, this might help: https://www.autohotkey.com/boards/viewtopic.php?f=6&t=75196
Paul H 81
Posts: 9
Joined: 11 Dec 2019, 22:19

Re: Get spreadsheet info

30 Apr 2020, 17:39

Wow!! This is incredible! Like I mentioned, I really am a beginner here. I tried inputting the URL for the spreadsheet that I want to access but could not get it to work. What are the lines in your code that need to be replaced by info from my google sheet? I replaced the doc := and the sht := but that didn't work.

Code: Select all

settimer, fetch, -50

;NBA sheet: https docs.google.com /spreadsheets/d/1aBohx1LumhF6UICZgnI6iao8YjgK72_qnfU8O-_szqo/edit#gid=7311341978  Broken Link for safety
;Sheet I want to access: https docs.google.com /spreadsheets/d/1Jzk3LXp3mRFeywqaBGWf0SiO15D9NbWWjEUrx01a70o/edit#gid=1549904757  Broken Link for safety
doc := "1Jzk3LXp3mRFeywqaBGWf0SiO15D9NbWWjEUrx01a70o", sht := "1549904757"

InputBox, needle,enter cell address, , , 175, 105
ifequal, needle, , exitapp

col := regexreplace(needle,"(\D+)\d+","$L1")
row := regexreplace(needle,"\D+(\d+)","$1")

Loop, Parse, col
	{
    tmp := Asc(A_LoopField) - 96
    col += tmp * (26 ** (StrLen(col) - A_Index))
	}
for x,y in strsplit(oVar,"`n","`r")
	if (x = row)
		loop, parse, y, CSV
			if (a_index = col)
				res := a_loopfield
msgbox % res
return

fetch:
URLDownloadToFile,% "https docs.google.com /spreadsheets/d/"  Broken Link for safety doc "/export?format=csv&id=" doc "&gid=" sht, tmp.csv
while !FileExist(a_scriptdir "\tmp.csv")
	sleep, 50
fileread, oVar, tmp.csv																				; change to your needs
FileDelete, tmp.csv
return

Also, I'd like to be able to put an ID number (the spreadsheet has around 4000 unique ID numbers in column A) into the input box and have it return data from column F. Is something like that possible? And if you don't have time respond to someone so completely clueless, I completely understand ;)

Thank you so much for responding!
User avatar
flyingDman
Posts: 881
Joined: 29 Sep 2013, 19:01

Re: Get spreadsheet info

30 Apr 2020, 18:27

The Doc ID is used twice 2 x and the sheet # is at the very end:
20200430_162537clip.jpg
20200430_162537clip.jpg (19.85 KiB) Viewed 634 times
In addition the spreadsheet's permissions need to be set to at least "anyone with the link can view" .
Paul H 81
Posts: 9
Joined: 11 Dec 2019, 22:19

Re: Get spreadsheet info

01 May 2020, 08:38

Not sure if you've watched The Office but there's one where Michael doesn't understand something and asks Oscar to explain it to him like he's a 6 year old. When he still doesn't understand it, he asked him to explain it like he's a 4 year old. This is a lot like that. Again, if you don't have time for explanations, I totally understand. This is what I have that's not working:

Code: Select all

settimer, fetch, -50

doc := "1Jzk3LXp3mRFeywqaBGWf0SiO15D9NbWWjEUrx01a70o", sht := "1549904757"

InputBox, needle,enter cell address, , , 175, 105
ifequal, needle, , exitapp

col := regexreplace(needle,"(\D+)\d+","$L1")
row := regexreplace(needle,"\D+(\d+)","$1")

Loop, Parse, col
	{
    tmp := Asc(A_LoopField) - 96
    col += tmp * (26 ** (StrLen(col) - A_Index))
	}
for x,y in strsplit(oVar,"`n","`r")
	if (x = row)
		loop, parse, y, CSV
			if (a_index = col)
				res := a_loopfield
msgbox % res
return
;Sheet I want to access: https docs.google.com /spreadsheets/d/1Jzk3LXp3mRFeywqaBGWf0SiO15D9NbWWjEUrx01a70o/edit#gid=1549904757  Broken Link for safety


fetch:
URLDownloadToFile,% "https docs.google.com /spreadsheets/d/1Jzk3LXp3mRFeywqaBGWf0SiO15D9NbWWjEUrx01a70o/export?format=csv&id=1Jzk3LXp3mRFeywqaBGWf0SiO15D9NbWWjEUrx01a70o&gid=1549904757"  Broken Link for safety sht, tmp.csv
while !FileExist(a_scriptdir "\tmp.csv")
	sleep, 50
fileread, oVar, tmp.csv																				; change to your needs
FileDelete, tmp.csv
return

Also, any idea what I could do to return data from an offset cell? Here is the script I'm using for this in excel:
#8::

Send, ^c
oExcel := ComObjActive("Excel.Application") ;creates a handle to your currently active excel sheet
Var1 := Clipboard
FoundCell := ComObjActive("Excel.Application").ActiveSheet.Range("A:A").Find(Var1)
MsgBox, 0, Overdue Lesson:, % FoundCell.Offset(0, 5).Value
return
Thank you for your time!
User avatar
flyingDman
Posts: 881
Joined: 29 Sep 2013, 19:01

Re: Get spreadsheet info

01 May 2020, 10:04

I believe the permissions on that spreadsheet wont allow you to download a CSV programmatically. Permissions need to be set to at least "anyone with the link can view". I know that for certain applications, this would not be possible, however for many other applications it's just fine. To access those settings, click on the green "Share" button on the top right, then click on advanced at the bottom right, then click on "Change in the "who has access" section and change it to "On - Anyone with the link".
Again, if you do not want the data exposed, do not do this....
Paul H 81
Posts: 9
Joined: 11 Dec 2019, 22:19

Re: Get spreadsheet info

03 May 2020, 10:40

Still not working. I give up though - thank you for your time!
User avatar
littlegandhi1199
Posts: 187
Joined: 29 Aug 2016, 23:58

Re: Get spreadsheet info

10 May 2020, 04:35

Paul H 81 wrote:
03 May 2020, 10:40
Still not working. I give up though - thank you for your time!
What's wrong with just speeding it up a little. Select all your cells Ctrl+A right? then copy and run that on a hotkey.
Pull the cells out and do what you want with them

Code: Select all

loop, parse, Clipboard, `n
{
loop, parse, A_Loopfield, %A_Tab%
{
cell := A_Loopfield
}
}
Script Backups on every Execution :mrgreen:
https://www.autohotkey.com/boards/viewtopic.php?f=6&t=75767&p=328155#p328155

Scrabble Solver 4-15 letter word outputs ( :crazy: # of inputs)
https://www.autohotkey.com/boards/viewtopic.php?f=19&t=34285

Return to “Ask For Help”

Who is online

Users browsing this forum: Bing [Bot], Darkspectro, Google [Bot], jackn11, kczx3, mikeyww, songdg, teadrinker and 43 guests