GoogleAPIsVBA - Free Version - Detailed walk through

Subscribe Now!
New videos every Weekend!

Like what I do? Donate

Did I help you? Did one of my tutorials save you sometime? 

You can say thank you by buying me a cup of coffee, I go through a lot of it.

Help keep Greater Good resources free for everyone. Please donate today. 

This page is not monitored so for questions please comment on the youtube video page. For suggestions email vbaa2z.team@gmail.com

Download the library from video description: 

Working with Drive https://youtu.be/t0b1U06dcio

Working with Google Sheets https://youtu.be/IemiXN2M6xg

Functions with parameters and return type

'----------------------------------------------------
'
'Updates | Change:
'Version: 1.0.1 [08-16-2020]
'Author - Youtube.com/vbaa2z | L Pamai
'
'AccountCredentials           - > StartService
'Updates: ListFilesandFodlers - > ListFilesFolders
'Class renamed to include more Google APIs
'Updates: GoogleDrive         - > GoogleapisVBA
'Google Sheets APIs Added
'---------------------------------------------------1.0.0
'StartService(infoArr() As String)
'ListFilesFolders(pDelimiter As String) As String()
'UploadFile(parentid As String, filetoUpload As String) As Boolean
'CreateFolder(pFolder_ID As String) As String
'DownloadFile(pFile_ID As String, DesFolder As String) As String
'CreateSubFolder(parentid As String, subfolderName As String) As String
'ObjDelete(FileID As String) As String
'---------------------------------------------------1.0.1
'SheetNames(sSpreadsheetId As String) As String()
'AppendData(pSheetID As String, pSheetName As String, pData() As String) As String
'Retrieve(pSheetID As String, pSheetName As String, pRange As String) As String(2 Dimensions)
'     UBound(DataList(), 1) ----- < ROWS
'     UBound(DataList(), 2) ----- < COLUMNS
'LastServiceInfo()
'---------------------------------------------------

Sample code: VBA and Google Sheets

Option Explicit

'Reference to -- > GoogleapisVBA.tlb
'C:\Program Files (x86)\VBA A2Z\GoogleapisVBAVBA\

Const pSheetID As String = "1kladw3Bcgh50cKedPdohbbkBOfR5k7Mgn1jnB9BwfLQ"

Type Credential
   Type As String
   KeyFile As String
   Email As String
End Type

Dim myArr(2) As String

Function CredInfo() As Variant 'As String
'Type:     Input either 'user' or 'service'
'KeyFile:  key file full path. .p12 or .json file
'Email:    This is needed only when file is credential file is .p12 and type is 'service'
'-------------------------------------------------------

Dim c As Credential

c.Type = "user"
c.KeyFile = "C:\Users\LP\Desktop\libtst\gapisVBA-cre\rdCre.json"
c.Email = "service@sheetsapivba.iam.gserviceaccount.com"

myArr(0) = c.Type: myArr(1) = c.KeyFile: myArr(2) = c.Email

CredInfo = myArr

End Function

Function SetInfo()
    Dim gDrive As New GoogleapisVBA.VBALib
    gDrive.StartService (CredInfo)
    MsgBox gDrive.GetAccInfo, vbInformation, "GoogleapisVBA Lib for VBA"
    Set gDrive = Nothing
End Function

Function Test_PrintSheetNames()

Dim gSheet As New GoogleapisVBA.VBALib, vList
On Error GoTo errX

gSheet.StartService (CredInfo)
vList = gSheet.SheetNames(pSheetID)

Sheet2.Select
clearData

[A1].Resize(UBound(vList) + 1, 1) = Application.WorksheetFunction.Transpose(vList)

Set gSheet = Nothing

Exit Function
errX:
Debug.Print Err.Description & Err.Number

End Function


Function Test_Append()
Dim vData(3) As String
Dim SheetName As String

SheetName = "Transactions"

vData(0) = "12/2/2020"
vData(1) = "CLT-10"
vData(2) = "38845.45"
vData(3) = "EX"

On Error GoTo errX
Dim gSheet As New GoogleapisVBA.VBALib

gSheet.StartService (CredInfo)
Call gSheet.AppendData(pSheetID, SheetName, vData())

Set gSheet = Nothing

Exit Function
errX:
Debug.Print Err.Description & Err.Number

End Function


Function Test_Retrieve()

Dim SheetName As String, i As Long, j As Long
Dim vRange As String

Sheet2.Activate
vRange = "Transactions!A1:D"
On Error GoTo errX

Dim gSheet As New GoogleapisVBA.VBALib, shData

gSheet.StartService (CredInfo)
shData = gSheet.Retrieve(pSheetID, SheetName, vRange)

For i = LBound(shData, 1) To UBound(shData, 1)
    For j = LBound(shData, 2) To UBound(shData, 2)
      Debug.Print shData(i, j)
    Next j
    Debug.Print String(15, "~")
Next

'Debug.Print ">" & shData
'Stop

clearData

[A1].Resize(UBound(shData, 1) + 1, UBound(shData, 2) + 1) = (shData)

AutoData

Set gSheet = Nothing

Exit Function
errX:
Debug.Print Err.Description & Err.Number

End Function


Sub AutoData()
   Sheet2.Select
   Cells.Select
   Cells.EntireColumn.AutoFit
End Sub

Sub clearData()
   Sheet2.Select
   Cells.Select
   Selection.ClearContents
End Sub

Sample code: VBA and Google Drive

Option Explicit

Dim ParentFolderID As String
Dim NewFolderName As String
Dim file_fullName As String
Dim fileID As String
Dim des_file_fullName As String

Sub TestCreateFolder()

Dim gDrive As New GoogleapisVBA.VBALib
Dim myList As Variant, i As Long

NewFolderName = "VBA Service Acc"
gDrive.StartService (CredInfo)
MsgBox gDrive.CreateFolder(NewFolderName)

Set gDrive = Nothing

End Sub
Sub TestCreateSubFolder()

Dim gDrive As New GoogleapisVBA.VBALib
Dim myList As Variant, i As Long

ParentFolderID = "1ILgEIP8CMl5cW_YUYpMasy0xRbkhy5pZ"
NewFolderName = "Sub Folder Demo"

gDrive.StartService (CredInfo)
MsgBox gDrive.CreateSubFolder(ParentFolderID, NewFolderName)

Set gDrive = Nothing

End Sub

Public Function GoogleapisVBAList()

Dim lngResult As Long
Dim gDrive As GoogleapisVBA.VBALib
Dim myList As Variant, i As Long, j As Long, c As Long, lr As Long, cur_item As String, cur_item_split() As String

Set gDrive = New GoogleapisVBA.VBALib
gDrive.StartService (CredInfo)

setHeaders
    
myList = gDrive.ListFilesFolders(pdelimiter:="~")

For i = LBound(myList) To UBound(myList)
    
    cur_item = myList(i) 'file/folder attributes saperated by delimiter
    
    'write to cells
    lr = Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Row
    Range("G" & lr).Value = cur_item
    
    cur_item_split = Split(cur_item, "~")
    c = 8
    
    For j = 0 To UBound(cur_item_split)
        Cells(lr, c).Value = cur_item_split(j)
        c = c + 1
    Next j

Next

Set gDrive = Nothing

End Function

Public Function GoogleapisVBAUploadFile()

Dim gDrive As GoogleapisVBA.VBALib
Set gDrive = New GoogleapisVBA.VBALib
Dim myList As Boolean

'file_fullName = "C:\Users\LP\source\repos\NETEXT_VBA\GoogleapisVBA\GoogleapisVBAVBA\Release\fileupload\e-cource.jpg"
file_fullName = "C:\Users\LP\source\repos\NETEXT_VBA\GoogleapisVBA\GoogleapisVBAVBA\Release\fileupload\e-cource -3.mp4"
ParentFolderID = "1ILgEIP8CMl5cW_YUYpMasy0xRbkhy5pZ"

gDrive.StartService (CredInfo)
myList = gDrive.uploadFile(ParentFolderID, file_fullName)
MsgBox "Upload Status:" & myList & ". File: " & file_fullName

Set gDrive = Nothing

End Function

Sub TestDownloadFile()

Dim gDrive As New GoogleapisVBA.VBALib
Dim myList As Variant, i As Long

des_file_fullName = "C:\Users\LP\source\repos\NETEXT_VBA\GoogleapisVBA\GoogleapisVBAVBA\Release\filedownload\"
fileID = "1Stt-JqTouFcvgUyyRyrU9aaRL6qjSL6N"

gDrive.StartService (CredInfo)
MsgBox gDrive.DownloadFile(fileID, des_file_fullName)

Set gDrive = Nothing

End Sub
Sub TestObjDelete()

Dim gDrive As New GoogleapisVBA.VBALib
Dim myList As Variant, i As Long

fileID = "1Stt-JqTouFcvgUyyRyrU9aaRL6qjSL6N"

gDrive.StartService (CredInfo)
MsgBox gDrive.ObjDelete(fileID)

Set gDrive = Nothing

End Sub

Sub setHeaders()
Sheet1.Activate
Dim arrayData() As Variant
   Columns("G:M").ClearContents
   arrayData = Array("Object List", "File Name", "File ID", "Parent ID", "Last Modified", "Size", "Type")
   [G1].Resize(1, UBound(arrayData) + 1) = (arrayData)
End Sub