Google Sheets API

[userXeo1]

After searching for an example of AHK code for google sheets, I was not able to find anything that works on API level. I have been using class google drive https://gist.github.com/tmplinshi/b81e135a87c778a5ed16d9e83017c703 Thank you tmplinshi for wonderful script.

I have decided to work with this script to convert to google sheets. So far, I have made a little progress, but its a beginning. Credit to @tmplinshi. So far I was able to retrieve gsheet document and display contents of gsheet in JSON format in msgbox. ONLY client.getAccessToken() and client.get() work. Evrything else is from original script.

Any suggestions on my next steps? I am bit lost? Any help appreciated. Thank you.

Code: Select all

; Google Sheets API test

#NoEnv
SetWorkingDir %A_ScriptDir%
SetBatchLines, -1

client_id     := "xxxxxxx"
client_secret := "xxxxxxxxxx"
refresh_token := "xxxxxxxxxxxxxxxx"

client := new gsheets(client_id, client_secret, refresh_token)

msgbox % client.getAccessToken()

MsgBox % Clipboard := client.get("xxxxxID_gheet_xxxxxxxxxxx?includeGridData=true&ranges=A1")
ExitApp


; https://developers.google.com/drive/api/v3/reference

class gsheets
{
	whr     := ComObjCreate("WinHttp.WinHttpRequest.5.1")
	;baseUrl := "https://www.googleapis.com/auth/spreadsheets"
	baseUrl := "https://sheets.googleapis.com/v4/spreadsheets/"
	__New(client_id, client_secret, refresh_token)
	{
		this.client_id     := client_id
		this.client_secret := client_secret
		this.refresh_token := refresh_token
	}
	
	getAccessToken()
	{
		body := { "client_id": this.client_id
		        , "client_secret": this.client_secret
		        , "refresh_token": this.refresh_token
		        , "grant_type": "refresh_token" }
		
		result := this.http( "POST"
		                   , "https://www.googleapis.com/oauth2/v4/token"
		                   , Jxon_Dump(body)
		                   , {"Content-Type": "application/json"} )
		
		return this.access_token := Jxon_Load(result).access_token
		/*
			{
			  "access_token": "xxxxxxxxxxxxxxxxx",
			  "expires_in": 3599,
			  "scope": "https://www.googleapis.com/auth/drive",
			  "token_type": "Bearer"
			}
		*/
	}
	
	get(resource)
	{
		return this.http("GET", this.baseUrl . resource )
	}
	
	post(resource, data, oHeaders := "")
	{
		return this.http("POST", this.baseUrl . resource, data, oHeaders)
	}
	
	simpileUpload(fileName)
	{
		url := "https://www.googleapis.com/upload/drive/v3/files?uploadType=media"
		return this.http("POST", url, this.FileToSafeArray(fileName))
	}
	
	updateFile(fileId, fileName)
	{
		url := "https://www.googleapis.com/upload/drive/v3/files/" fileId "?uploadType=media"
		return this.http("PATCH", url, this.FileToSafeArray(fileName))
	}
	
	getFileId(name)
	{
		res := "/files?pageSize=1&q=" this.URIEncode("name='" name "' or name contains '" name "'")
		out := Jxon_Load( this.get(res) )
		return out.files.1.id
	}
	
	upload(fileName, obj_Metadata := "")
	{
		if !FileExist(fileName)
			throw
		
		if !IsObject(obj_Metadata)
		{
			fName := RegExReplace(fileName, ".*[\\/]")
			obj_Metadata := {name: fName}
		}
		
		url := "https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart"
		obj := { "whatever": [fileName]
		       , "i_am_json": {json: Jxon_Dump(obj_Metadata)} }
		CreateFormData(retData, retHeader, obj)
		return this.http("POST", url, retData, {"Content-Type": retHeader})
	}
	
	downloadByName(name)
	{
		res := "/files?pageSize=1&q=" this.URIEncode("name='" name "' or name contains '" name "'")
		out := Jxon_Load( this.get(res) )
		if (out.files.1.id)
			return this.downloadById(out.files.1.id, out.files.1.name)
	}
	
	downloadById(fileId, fileName := "")
	{
		if (fileName = "")
			fileName := Jxon_Load( this.get("/files/" fileId) ).files.1.name
		
		this.get("/files/" fileId "?alt=media")
		this.StreamToFile(this.whr.ResponseStream, fileName)
		return fileName
	}
	
	http(method, url, postData="", obj_headers="")
	{
		whr := this.whr
		
		whr.Open(method, url, true)
		
		if (this.proxy)
			whr.SetProxy(2, this.proxy)
		
		for k, v in obj_headers {
			whr.SetRequestHeader(k, v)
		}
		if (this.access_token) {
			whr.SetRequestHeader("Authorization", "Bearer " . this.access_token)
		}
		if (method = "POST") && !obj_headers.HasKey("Content-Type") {
			whr.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded")
		}
		
		whr.Send(postData)
		whr.WaitForResponse()
		
		if (whr.Status = 200)
			return whr.ResponseText
		else
			throw whr.Status " " whr.StatusText "`n" whr.GetAllResponseHeaders() "`n`n" whr.ResponseText
	}
	
	FileToSafeArray(fileName) {
		File := FileOpen(FileName, "r")
		safeArr := ComObjArray(0x11, File.length) ; Create SAFEARRAY = VT_ARRAY|VT_UI1
		File.RawRead(NumGet(ComObjValue(safeArr) + 8 + A_PtrSize), File.length) ; read raw data
		File.Close()
		return safeArr
	}
	
	; https://autohotkey.com/board/topic/65701-urldownloadtofile-saving-download-to-default-name/?p=650775
	; StreamToFile(whr.ResponseStream, "fileName")
	StreamToFile(Stream, fileName)  {
		If (ComObjType(Stream) != 0xD)  
			Return 0
		pIStream := ComObjQuery(Stream, "{0000000c-0000-0000-C000-000000000046}")
		oFile := FileOpen(fileName, "w")
		While (cbRead != 0)
			VarSetCapacity(Buffer, 8192)
			, DllCall(NumGet(NumGet(pIStream + 0) + 3 * A_PtrSize)
			, "ptr", pIStream, "ptr", &Buffer, "uint", 8192, "ptr*", cbRead)
			, oFile.RawWrite(&Buffer, cbRead)
		Return 1, ObjRelease(pIStream), oFile.Close()      
	}
	
	; https://www.autohotkey.com/boards/viewtopic.php?t=63835
	URIEncode(str, encoding := "UTF-8")  {
		VarSetCapacity(var, StrPut(str, encoding))
		StrPut(str, &var, encoding)
		
		While code := NumGet(Var, A_Index - 1, "UChar")  {
			bool := (code > 0x7F || code < 0x30 || code = 0x3D)
			UrlStr .= bool ? "%" . Format("{:02X}", code) : Chr(code)
		}
		Return UrlStr
	}
}



; ===============================================================================

; CreateFormData
; 2020-06-08 - Added a temporary approach to handling the json content-type
;	           example: objParam := { "key": {json: "{'k':'v'}"} }
;
/*
	CreateFormData - Creates "multipart/form-data" for http post
	https://www.autohotkey.com/boards/viewtopic.php?t=7647

	Usage: CreateFormData(ByRef retData, ByRef retHeader, objParam)

		retData   - (out) Data used for HTTP POST.
		retHeader - (out) Content-Type header used for HTTP POST.
		objParam  - (in)  An object defines the form parameters.

		            To specify files, use array as the value. Example:
		                objParam := { "key1": "value1"
		                            , "upload[]": ["1.png", "2.png"] }

	Requirements: BinArr.ahk -- https://gist.github.com/tmplinshi/a97d9a99b9aa5a65fd20
	Version    :      / 2020-06-08 - Added a temporary approach to handling the json content-type
	                                 example: objParam := { "key": {json: "{'k':'v'}"} }
	             1.30 / 2019-01-13 - The file parameters are now placed at the end of the retData
	             1.20 / 2016-06-17 - Added CreateFormData_WinInet(), which can be used for VxE's HTTPRequest().
	             1.10 / 2015-06-23 - Fixed a bug
	             1.00 / 2015-05-14
*/

; Used for WinHttp.WinHttpRequest.5.1, Msxml2.XMLHTTP ...
CreateFormData(ByRef retData, ByRef retHeader, objParam) {
	New CreateFormData(retData, retHeader, objParam)
}

; Used for WinInet
CreateFormData_WinInet(ByRef retData, ByRef retHeader, objParam) {
	New CreateFormData(safeArr, retHeader, objParam)

	size := safeArr.MaxIndex() + 1
	VarSetCapacity(retData, size, 1)
	DllCall("oleaut32\SafeArrayAccessData", "ptr", ComObjValue(safeArr), "ptr*", pdata)
	DllCall("RtlMoveMemory", "ptr", &retData, "ptr", pdata, "ptr", size)
	DllCall("oleaut32\SafeArrayUnaccessData", "ptr", ComObjValue(safeArr))
}

Class CreateFormData {

	__New(ByRef retData, ByRef retHeader, objParam) {

		CRLF := "`r`n"

		Boundary := this.RandomBoundary()
		BoundaryLine := "------------------------------" . Boundary

		; Loop input paramters
		binArrs := []
		fileArrs := []
		For k, v in objParam
		{
			If IsObject(v) {
				if v.HasKey("json")
				{
					str := BoundaryLine . CRLF
					     . "Content-Type: application/json" . CRLF . CRLF
					     . v.json . CRLF
					binArrs.Push( BinArr_FromString(str) )
				}
				else
				{
					For i, FileName in v
					{
						str := BoundaryLine . CRLF
						     . "Content-Disposition: form-data; name=""" . k . """; filename=""" . FileName . """" . CRLF
						     . "Content-Type: " . this.MimeType(FileName) . CRLF . CRLF
						fileArrs.Push( BinArr_FromString(str) )
						fileArrs.Push( BinArr_FromFile(FileName) )
						fileArrs.Push( BinArr_FromString(CRLF) )
					}
				}
				
			} Else {
				str := BoundaryLine . CRLF
				     . "Content-Disposition: form-data; name=""" . k """" . CRLF . CRLF
				     . v . CRLF
				binArrs.Push( BinArr_FromString(str) )
			}
		}

		binArrs.push( fileArrs* )

		str := BoundaryLine . "--" . CRLF
		binArrs.Push( BinArr_FromString(str) )

		retData := BinArr_Join(binArrs*)
		retHeader := "multipart/form-data; boundary=----------------------------" . Boundary
	}

	RandomBoundary() {
		str := "0|1|2|3|4|5|6|7|8|9|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z"
		Sort, str, D| Random
		str := StrReplace(str, "|")
		Return SubStr(str, 1, 12)
	}

	MimeType(FileName) {
		n := FileOpen(FileName, "r").ReadUInt()
		Return (n        = 0x474E5089) ? "image/png"
		     : (n        = 0x38464947) ? "image/gif"
		     : (n&0xFFFF = 0x4D42    ) ? "image/bmp"
		     : (n&0xFFFF = 0xD8FF    ) ? "image/jpeg"
		     : (n&0xFFFF = 0x4949    ) ? "image/tiff"
		     : (n&0xFFFF = 0x4D4D    ) ? "image/tiff"
		     : "application/octet-stream"
	}

}

; ===============================================================================
BinArr_FromString(str) {
	oADO := ComObjCreate("ADODB.Stream")

	oADO.Type := 2 ; adTypeText
	oADO.Mode := 3 ; adModeReadWrite
	oADO.Open
	oADO.Charset := "UTF-8"
	oADO.WriteText(str)

	oADO.Position := 0
	oADO.Type := 1 ; adTypeBinary
	oADO.Position := 3 ; Skip UTF-8 BOM
	return oADO.Read, oADO.Close
}

BinArr_FromFile(FileName) {
	oADO := ComObjCreate("ADODB.Stream")

	oADO.Type := 1 ; adTypeBinary
	oADO.Open
	oADO.LoadFromFile(FileName)
	return oADO.Read, oADO.Close
}

BinArr_Join(Arrays*) {
	oADO := ComObjCreate("ADODB.Stream")

	oADO.Type := 1 ; adTypeBinary
	oADO.Mode := 3 ; adModeReadWrite
	oADO.Open
	For i, arr in Arrays
		oADO.Write(arr)
	oADO.Position := 0
	return oADO.Read, oADO.Close
}

BinArr_ToString(BinArr, Encoding := "UTF-8") {
	oADO := ComObjCreate("ADODB.Stream")

	oADO.Type := 1 ; adTypeBinary
	oADO.Mode := 3 ; adModeReadWrite
	oADO.Open
	oADO.Write(BinArr)

	oADO.Position := 0
	oADO.Type := 2 ; adTypeText
	oADO.Charset  := Encoding 
	return oADO.ReadText, oADO.Close
}

BinArr_ToFile(BinArr, FileName) {
	oADO := ComObjCreate("ADODB.Stream")

	oADO.Type := 1 ; adTypeBinary
	oADO.Open
	oADO.Write(BinArr)
	oADO.SaveToFile(FileName, 2)
	oADO.Close
}

; ==================================================================================
; by Coco, https://www.autohotkey.com/boards/viewtopic.php?t=627
Jxon_Load(ByRef src, args*)
{
	static q := Chr(34)

	key := "", is_key := false
	stack := [ tree := [] ]
	is_arr := { (tree): 1 }
	next := q . "{[01234567890-tfn"
	pos := 0
	while ( (ch := SubStr(src, ++pos, 1)) != "" )
	{
		if InStr(" `t`n`r", ch)
			continue
		if !InStr(next, ch, true)
		{
			ln := ObjLength(StrSplit(SubStr(src, 1, pos), "`n"))
			col := pos - InStr(src, "`n",, -(StrLen(src)-pos+1))

			msg := Format("{}: line {} col {} (char {})"
			,   (next == "")      ? ["Extra data", ch := SubStr(src, pos)][1]
			  : (next == "'")     ? "Unterminated string starting at"
			  : (next == "\")     ? "Invalid \escape"
			  : (next == ":")     ? "Expecting ':' delimiter"
			  : (next == q)       ? "Expecting object key enclosed in double quotes"
			  : (next == q . "}") ? "Expecting object key enclosed in double quotes or object closing '}'"
			  : (next == ",}")    ? "Expecting ',' delimiter or object closing '}'"
			  : (next == ",]")    ? "Expecting ',' delimiter or array closing ']'"
			  : [ "Expecting JSON value(string, number, [true, false, null], object or array)"
			    , ch := SubStr(src, pos, (SubStr(src, pos)~="[\]\},\s]|$")-1) ][1]
			, ln, col, pos)

			throw Exception(msg, -1, ch)
		}

		is_array := is_arr[obj := stack[1]]

		if i := InStr("{[", ch)
		{
			val := (proto := args[i]) ? new proto : {}
			is_array? ObjPush(obj, val) : obj[key] := val
			ObjInsertAt(stack, 1, val)
			
			is_arr[val] := !(is_key := ch == "{")
			next := q . (is_key ? "}" : "{[]0123456789-tfn")
		}

		else if InStr("}]", ch)
		{
			ObjRemoveAt(stack, 1)
			next := stack[1]==tree ? "" : is_arr[stack[1]] ? ",]" : ",}"
		}

		else if InStr(",:", ch)
		{
			is_key := (!is_array && ch == ",")
			next := is_key ? q : q . "{[0123456789-tfn"
		}

		else ; string | number | true | false | null
		{
			if (ch == q) ; string
			{
				i := pos
				while i := InStr(src, q,, i+1)
				{
					val := StrReplace(SubStr(src, pos+1, i-pos-1), "\\", "\u005C")
					static end := A_AhkVersion<"2" ? 0 : -1
					if (SubStr(val, end) != "\")
						break
				}
				if !i ? (pos--, next := "'") : 0
					continue

				pos := i ; update pos

				  val := StrReplace(val,    "\/",  "/")
				, val := StrReplace(val, "\" . q,    q)
				, val := StrReplace(val,    "\b", "`b")
				, val := StrReplace(val,    "\f", "`f")
				, val := StrReplace(val,    "\n", "`n")
				, val := StrReplace(val,    "\r", "`r")
				, val := StrReplace(val,    "\t", "`t")

				i := 0
				while i := InStr(val, "\",, i+1)
				{
					if (SubStr(val, i+1, 1) != "u") ? (pos -= StrLen(SubStr(val, i)), next := "\") : 0
						continue 2

					; \uXXXX - JSON unicode escape sequence
					xxxx := Abs("0x" . SubStr(val, i+2, 4))
					if (A_IsUnicode || xxxx < 0x100)
						val := SubStr(val, 1, i-1) . Chr(xxxx) . SubStr(val, i+6)
				}

				if is_key
				{
					key := val, next := ":"
					continue
				}
			}

			else ; number | true | false | null
			{
				val := SubStr(src, pos, i := RegExMatch(src, "[\]\},\s]|$",, pos)-pos)
			
			; For numerical values, numerify integers and keep floats as is.
			; I'm not yet sure if I should numerify floats in v2.0-a ...
				static number := "number", integer := "integer"
				if val is %number%
				{
					if val is %integer%
						val += 0
				}
			; in v1.1, true,false,A_PtrSize,A_IsUnicode,A_Index,A_EventInfo,
			; SOMETIMES return strings due to certain optimizations. Since it
			; is just 'SOMETIMES', numerify to be consistent w/ v2.0-a
				else if (val == "true" || val == "false")
					val := %val% + 0
			; AHK_H has built-in null, can't do 'val := %value%' where value == "null"
			; as it would raise an exception in AHK_H(overriding built-in var)
				else if (val == "null")
					val := ""
			; any other values are invalid, continue to trigger error
				else if (pos--, next := "#")
					continue
				
				pos += i-1
			}
			
			is_array? ObjPush(obj, val) : obj[key] := val
			next := obj==tree ? "" : is_array ? ",]" : ",}"
		}
	}

	return tree[1]
}

Jxon_Dump(obj, indent:="", lvl:=1)
{
	static q := Chr(34)
	
	if IsObject(obj)
	{
		static Type := Func("Type")
		if Type ? (Type.Call(obj) != "Object") : (ObjGetCapacity(obj) == "")
			throw Exception("Object type not supported.", -1, Format("<Object at 0x{:p}>", &obj))
		
		is_array := 0
		for k in obj
			is_array := k == A_Index
		until !is_array
		
		static integer := "integer"
		if indent is %integer%
		{
			if (indent < 0)
				throw Exception("Indent parameter must be a postive integer.", -1, indent)
			spaces := indent, indent := ""
			Loop % spaces
				indent .= " "
		}
		indt := ""
		Loop, % indent ? lvl : 0
			indt .= indent
		
		lvl += 1, out := "" ; Make #Warn happy
		for k, v in obj
		{
			if IsObject(k) || (k == "")
				throw Exception("Invalid object key.", -1, k ? Format("<Object at 0x{:p}>", &obj) : "<blank>")
			
			if !is_array
				out .= ( ObjGetCapacity([k], 1) ? Jxon_Dump(k) : q . k . q ) ;// key
				    .  ( indent ? ": " : ":" ) ; token + padding
			out .= Jxon_Dump(v, indent, lvl) ; value
			    .  ( indent ? ",`n" . indt : "," ) ; token + indent
		}
		
		if (out != "")
		{
			out := Trim(out, ",`n" . indent)
			if (indent != "")
				out := "`n" . indt . out . "`n" . SubStr(indt, StrLen(indent)+1)
		}
		
		return is_array ? "[" . out . "]" : "{" . out . "}"
	}
	
	; Number
	else if (ObjGetCapacity([obj], 1) == "")
		return obj
	
	; String (null -> not supported by AHK)
	if (obj != "")
	{
		obj := StrReplace(obj,  "\",    "\\")
		, obj := StrReplace(obj,  "/",    "\/")
		, obj := StrReplace(obj,    q, "\" . q)
		, obj := StrReplace(obj, "`b",    "\b")
		, obj := StrReplace(obj, "`f",    "\f")
		, obj := StrReplace(obj, "`n",    "\n")
		, obj := StrReplace(obj, "`r",    "\r")
		, obj := StrReplace(obj, "`t",    "\t")
		
		static needle := (A_AhkVersion<"2" ? "O)" : "") . "[^\x20-\x7e]"
		while RegExMatch(obj, needle, m)
			obj := StrReplace(obj, m[0], Format("\u{:04X}", Ord(m[0])))
	}
	
	return q . obj . q
}

Help with API Keys:
http://www.howtosolvenow.com/api/how-to-create-your-google-drive-api-keys

Refresh token:
http://www.howtosolvenow.com/api/how-to-get-refresh-token-for-google-drive-api

[userXeo1]

A lot of research was done on AHK and GOOGLE SHEETS and according to my research not much can be done directly with AHK and Google Sheets API, especially when it comes to data search or manipulation (DB search, edit like functions). However, I have found out that Google Apps Script (GAS) can do all of this. Therefore, was looking for AHK to interact with Google Apps Script via APIs.

Today, I was able to accomplish this, AHK interacts with Google Apps Script, and Google Apps Script with Google Sheets all via API. Its bit complicated, but it works.

In a nutshell I was able to manipulate data of google sheets with AHK.

[AHKStudent]

A lot of research was done on AHK and GOOGLE SHEETS and according to my research not much can be done directly with AHK and Google Sheets API, especially when it comes to data search or manipulation (DB search, edit like functions). However, I have found out that Google Apps Script (GAS) can do all of this. Therefore, was looking for AHK to interact with Google Apps Script via APIs.

Today, I was able to accomplish this, AHK interacts with Google Apps Script, and Google Apps Script with Google Sheets all via API. Its bit complicated, but it works.

In a nutshell I was able to manipulate data of google sheets with AHK.

can you share some code please?

[userXeo1]

Hello, still have to clean up a bit of code, but here is primitive code that I have used:

To call a function:

Code: Select all

	fxnCall := "fFindRow"
	parms := objInq
	
	oaResp := client.post(script_id . ":run"
		, Jxon_Dump({ "function": fxnCall,"parameters": [parms]  ,"devMode": "True" })
		,{"Content-Type": "application/json"} )
	
	
	oaResults := Jxon_Load(oaResp)
	ToolTip, Results Recieved
	oaSrchRsltS := oaResults.response.result

This is a google script code:

Code: Select all

function fFindRow(srchParam){
    // console.log(srchParam);

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
  //var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  }

More to come.. if anyone is interested..

[cursofifi]

Hello, still have to clean up a bit of code, but here is primitive code that I have used:

To call a function:

Code: Select all

	fxnCall := "fFindRow"
	parms := objInq
	
	oaResp := client.post(script_id . ":run"
		, Jxon_Dump({ "function": fxnCall,"parameters": [parms]  ,"devMode": "True" })
		,{"Content-Type": "application/json"} )
	
	
	oaResults := Jxon_Load(oaResp)
	ToolTip, Results Recieved
	oaSrchRsltS := oaResults.response.result

This is a google script code:

Code: Select all

function fFindRow(srchParam){
    // console.log(srchParam);

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
  //var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  }

More to come.. if anyone is interested..

Hi, I'm very interested. Could you please share a complete script that connects to the Google Apps Script API? I couldn't use the example you provided. Thank you very much.