Get spreadsheet info

[jeeswg]

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

[Paul H 81]

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.

[littlegandhi1199]

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
}
}

[Paul H 81]

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

[flyingDman]

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]

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!

[flyingDman]

The Doc ID is used twice 2 x and the sheet # is at the very end:

20200430_162537clip.jpg (19.85 KiB) Viewed 2106 times

In addition the spreadsheet's permissions need to be set to at least "anyone with the link can view" .

[Paul H 81]

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!

[flyingDman]

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]

Still not working. I give up though - thank you for your time!

[littlegandhi1199]

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
}
}