VBA to update, set and clear data in Google Sheets 

Nope. For adding, deleting and updating the data methods, I use three class modules "UpdateValuesRequest", "ClearValuesRequest and "UpdateValuesRequest" respectively. Those class modules are used for constructing a proper Json format to be sent as a request body for each http request. You can learn more about how to build a http request for Google Sheets API here: Method: spreadsheets.values.update: developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update. From this link, you can understand how code works: Just create a http request with request headers and a request body (if any) and send the http request to a Google Sheets API endpoint URI.

@@dangnguyen9339 Thank you again dang nguyen. I'll try to understand everything.

Yes, we can. Watch this video here for details: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-JZHKzee5o60.html

Here is it: 1drv.ms/x/s!AmwM8BaWQzt0nM96-yq_kBFKBLZ0fg?e=d4aH0v

I have tested on many computers and no errors were detected. You may need to check those computers that produce the error to see if there are any referenced libraries that are missing and uncheck them, then try again. And, if possible, may you please upload a screenshot of that error to imgur.com/ so that I can have a more detailed look of what's gone wrong?

@@dangnguyen9339 I found the reason. It did not work in Excel 2013. I wil confirm if it does not work in 2016 also. In 2019 it works fine.

As far as I know, it is not possible to get UsedRange and/or last row/last column in a spreadsheet via Google Sheets API. Maybe you can write a Google Apps Script function and call it via Google Apps Script API. Take a look at this article: developers.google.com/apps-script/api/reference/rest/v1/scripts/run

@@dangnguyen9339 I see. How about if you want to add a new data? Do you have any methods in mind? Because in my other project when using the google form to connect to excel it is automatically added to the last row. I am not familiar with the GAS right now so using its code may take a while in my part.

@@MyAudioBookCompilation Maybe Method: spreadsheets.values.append is your best choice: developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append First, you need to create a class module named AppendValuesRequest with the following content: Option Explicit Public Enum InsertDataOption OVERWRITE INSERT_ROWS End Enum Private m_SpreadsheetId As String Private m_Range As String Private m_ValueInputOption As ValueInputOption Private m_InsertDataOption As InsertDataOption Private m_IncludeValuesInResponse As String Private m_ResponseValueRenderOption As RangeValueRenderOption Private m_ResponseDateTimeRenderOption As DateTimeRenderOption Public Property Get SpreadsheetId() As String SpreadsheetId = m_SpreadsheetId End Property Public Property Let SpreadsheetId(Value As String) m_SpreadsheetId = Value End Property Public Property Get Range() As String Range = m_Range End Property Public Property Let Range(Value As String) m_Range = Value End Property Public Property Get ValueInputOption() As ValueInputOption ValueInputOption = m_ValueInputOption End Property Public Property Let ValueInputOption(Value As ValueInputOption) m_ValueInputOption = Value End Property Public Property Get InsertDataOption() As InsertDataOption InsertDataOption = m_InsertDataOption End Property Public Property Let InsertDataOption(Value As InsertDataOption) m_InsertDataOption = Value End Property Public Property Get IncludeValuesInResponse() As String IncludeValuesInResponse = m_IncludeValuesInResponse End Property Public Property Let IncludeValuesInResponse(Value As String) m_IncludeValuesInResponse = Value End Property Public Property Get ResponseValueRenderOption() As RangeValueRenderOption ResponseValueRenderOption = m_ResponseValueRenderOption End Property Public Property Let ResponseValueRenderOption(Value As RangeValueRenderOption) m_ResponseValueRenderOption = Value End Property Public Property Get ResponseDateTimeRenderOption() As DateTimeRenderOption ResponseDateTimeRenderOption = m_ResponseDateTimeRenderOption End Property Public Property Let ResponseDateTimeRenderOption(Value As DateTimeRenderOption) m_ResponseDateTimeRenderOption = Value End Property Private Sub Class_Initialize() ValueInputOption = INPUT_VALUE_OPTION_UNSPECIFIED ResponseValueRenderOption = OPTION_FORMATTED_VALUE ResponseDateTimeRenderOption = SERIAL_NUMBER InsertDataOption = INSERT_ROWS End Sub Public Function ConstructQueryParameters() Dim strMajorDimension As String Dim strResponseValueRenderOption As String Dim strResponseDateTimeRenderOption As String Dim strValueInputOption As String Dim strIncludeValuesInResponse As String Dim strInsertDataOption As String Select Case ValueInputOption Case USER_ENTERED, INPUT_VALUE_OPTION_UNSPECIFIED strValueInputOption = "?valueInputOption=USER_ENTERED&" Case RAW strValueInputOption = "?valueInputOption=RAW&" End Select If IncludeValuesInResponse vbNullString Then strIncludeValuesInResponse = "includeValuesInResponse=" & IncludeValuesInResponse & "&" Select Case ResponseValueRenderOption Case OPTION_FORMATTED_VALUE strResponseValueRenderOption = "responseValueRenderOption=FORMATTED_VALUE&" Case OPTION_UNFORMATTED_VALUE strResponseValueRenderOption = "responseValueRenderOption=UNFORMATTED_VALUE&" Case OPTION_FORMULA strResponseValueRenderOption = "responseValueRenderOption=FORMULA&" End Select Select Case ResponseDateTimeRenderOption Case SERIAL_NUMBER strResponseDateTimeRenderOption = "responseDateTimeRenderOption=SERIAL_NUMBER&" Case FORMATTED_STRING strResponseValueRenderOption = "dateTimeRenderOption=FORMATTED_STRING&" End Select Select Case InsertDataOption Case OVERWRITE strInsertDataOption = "insertDataOption=OVERWRITE" Case INSERT_ROWS strInsertDataOption = "insertDataOption=INSERT_ROWS" End Select ConstructQueryParameters = SpreadsheetId & "/values/" & Range & ":append" & strValueInputOption & strIncludeValuesInResponse & strResponseValueRenderOption & strResponseDateTimeRenderOption & strInsertDataOption End Function Finally, write a subroutine to implement the above class module: Option Explicit Private Sub AppendData() Dim objGoogleOAuth2 As GoogleOAuth2 Set objGoogleOAuth2 = New GoogleOAuth2 With objGoogleOAuth2 .ApplicationName = "GoogleSheetTasks" '(Required) This is the name of folder in %APPDATA% that stores the access token granted from the user's authorization .ClientID = "364263102841-sqrgk7k3dv0tnt1eu2mdg1a5o4283110.apps.googleusercontent.com" 'Client ID, get it from console.cloud.google.com/ .ClientSecret = "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" 'Client Secret, get it from console.cloud.google.com/ .Scopes = Array("www.googleapis.com/auth/spreadsheets") 'Scopes for Google Sheets API, visit this website (developers.google.com/identity/protocols/oauth2/scopes#sheets) for more information .AuthorizeOAuth2 End With Dim objAppendValuesRequest As AppendValuesRequest Set objAppendValuesRequest = New AppendValuesRequest With objAppendValuesRequest .SpreadsheetId = "1t4J0vU57rAv8fPpGWc1J53mZC4A-m0UMfKNcEyVAvmE" ' Id of a spreadsheet .Range = "TongKetBanHang!A1:K30" 'The A1 notation of a range to search for a logical table of data. Values are appended after the last row of the table. .ValueInputOption = USER_ENTERED End With Dim arrValues() As Variant ' A 2d array of values to be appended to Range ReDim arrValues(1 To 1, 1 To 1) As Variant arrValues(1, 1) = "Hello" Dim objValuesRange As ValuesRange Set objValuesRange = New ValuesRange objValuesRange.Values = arrValues Dim objWinHttp As WinHttp.WinHttpRequest Set objWinHttp = New WinHttp.WinHttpRequest With objWinHttp .Open "POST", "sheets.googleapis.com/v4/spreadsheets/" & objAppendValuesRequest.ConstructQueryParameters, True .SetRequestHeader "Content-Type", "application/json" .SetRequestHeader "Accept", "application/json" .SetRequestHeader "Authorization", "Bearer " & objGoogleOAuth2.AccessToken .Send JsonConverter.ConvertToJson(objValuesRange.ToJson) .WaitForResponse If .Status = 200 Then Debug.Print .ResponseText End With End Sub For example, I want to append a value "Hello" to a range "A1:K30" in sheet "TongKetBanHang". Because the last used row and the last used column (that contains data) are 19 and 2 respectively, the value "Hello" will be inserted into cell B20.

@@dangnguyen9339 wow your so amazing. Thank you so much.

@@dangnguyen9339 It worked. You really are a genius. You are a pride of your country. Thank you so much.

I guess you use filter and find methods for your VBA project. But unfortunately, as of now Google Sheets API does not provide any methods for finding a row by value and/or get filtered data from a specific filterView. I found a workaround on Stack Overflow, see if it suits your need: stackoverflow.com/questions/49161249/google-sheets-api-how-to-find-a-row-by-value-and-update-its-content Besides VBA (Word, Excel and Outlook), I also learn C# but not too proficient.

@@dangnguyen9339 Great. C# is the next language that I am about to work on also and it is in my list. Right now I am not good with API so I think I need to understand this concept first to learn your codes. I seldom use the array also so I need to work with than often. My work usually deals with self automated application and/or local area network databases so i did not focus in using libraries to connect with the internet.

@@dangnguyen9339 Thank you once again for this information. I'll start with the basics first. Learning another library takes time. God bless.

Take a look at this video here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Rna6ZQ20QcE.html You can find a code sample in the video description.

Bạn vào diễn đàn Voz, mục Phần mềm là có nhé.

Take a look at this article: stackoverflow.com/questions/13984229/how-can-i-create-product-keys-for-vba-applications-so-that-illegal-distribution

Hi @Gökhan Boyacı! The code in this video is obsolete and has been superseded by a new .xlam add-in that covers full Google Sheets API methods. You can download it here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-HSvQMRqYMbc.html

The warning screen you saw is a normal behavior. Read this article here: support.google.com/cloud/answer/7454865

@@dangnguyen9339 I see but does the script error pop out? I see another script error that is similar when I used office 2013 and 2016. I have installed office 2021 right now but the same script error.

I have tried to click the Yes and No button everytime the script error pops out and i have clicked it 38 times. Is there a way that once we clicked the Back to safety then it will just close the user form right away? Thank you again Mr. Dang.

​@@MyAudioBookCompilation This "unverified app" screen appears because you have not verified your app with Google. Add your app privacy policy link, authorized domains, scopes, and submit your app to Google for review. Also, you need to set your app publishing status to production (external in this case).

@@dangnguyen9339 I see thank you. Do I need to pay for those?

Using Selenium in this case will not work, because any Chromium-based web browser launched using Selenium will be in test mode, and Google does not allow login in such case. Maybe something is wrong with the WebBrowser control in Office 2016 and earlier versions of Office. In this case, I can modify the code to directly working with Internet Explorer instead of using a userform with WebBrowser control embedded in it.

I have modified the code to directly working with Internet Explorer, but this error occurred: "This browser or app may not be secure", and hence I could not login to Google. I don't know why, but I think the only way to login to Google and get authorization code is using a userform with a WebBrowser control embedded (of cource it's still Internet Explorer) like I did before. Or, I can rewrite the code which: 1. Opens the login page using a default web browser (Edge, Chrome or Mozilla Firefox, etc.) via Shell or WScript.Shell. 2. Writes a code to display an InputBox and give instruction to the user on copying the URL on the web browser's address bar to that InputBox after he/she has logged in his/her Google account. But it's not the way I preferred because I want my VBA macros to fully control the web browser and require the user interaction as little as possible.

@@dangnguyen9339 Thank you for your effort. I do prefer your style also. I will just add a code that will prevent it from running if the excel version is earlier than Excel 2019, but the problem is even Excel 2019 is recognized as version 16 the same as Excel 2016. Do you have a code that will check if excel version is Excel 2019?

@@MyAudioBookCompilation I don't know how to check if Excel version is Excel 2019. Since Excel 2016, version 16.0 has represented for all later versions of Office apps. Internet Explorer is a pain in the ass when it comes to working with modern websites. If only Microsoft changed its mind and decided to write a COM DLL for Microsoft Edge so that that web browser could be automated in VBA (like WebView2 control in .NET), but I believe this hope will not and never become true.

@@dangnguyen9339 Your work is more than enough. Time will come that all users will use at least Windows 10 and Office 2019 so it is not a problem. By the way I have created a video showing that your contribution made my dream come true. In my next video i will mention your name and other people who were great like you. Thank you so much.

Đúng rồi thím, tất cả các bước, từ xác thực OAuth2, xin phép quyền truy cập từ người dùng cho đến tạo các HTTP request tương ứng dựa vào tài liệu của Google để tương tác với Google Sheets, đều do mình viết hết, ngoại trừ một số hàm/thư viện của bên thứ ba như VBA-JSON hoặc URLEncode, URLDecode.

@@dangnguyen9339 ô ơi, làm thêm video về việc tương tác dữ liệu từ googsheet về excel và ngược lại đi đọc code để làm theo khó hiểu quá

@@DungVu-di7dz Code này cũ quá rồi ông, hai tháng trước t có viết một thư viện .xlam nho nhỏ gói gọn hết đống code này rồi, hỗ trợ tất cả những phương thức của Google Sheets API. Tiếc là t lại không có thời gian đầu tư làm video cho thật chỉnh chu, có gì ông liên hệ với t qua Facebook: facebook.com/NguyenDang1995, để trao đổi chi tiết nhé.

@@dangnguyen9339 ô phát triển theo hướng k cần đăng nhập vào chorme được k, vì vụ đăng nhập nhiều khi lỗi