Singkronasi Excel Offline dengan Spreadsheet (Google Sheet) Unlimited (MS.2010) Tanpa Berlangganan

1. Buatlah Spreadsheet pada Google Sheet

2. Buatlah lembar kerja Apps Script untuk mengkoneksikan ke Excel Offline.

3. Pada lembar kerja Apps Script terdapat file default yaitu Code.gs.

4. Copy script di bawah ini ke Code.gs.

*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*

Masukkan Password Untuk Melihat Script (Password ada di dalam video)

// Script by Javabitpro
// www.javabutpro.com

function doGet(e) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    Utilities.sleep(2000);

  // Silahkan Sesuaikan Range Kolom Dengan Kebutuhan
  var data = sheet.getRange("A:H").getValues();
    var jsonData = JSON.stringify(data);
    return ContentService.createTextOutput(jsonData).setMimeType(ContentService.MimeType.JSON);
}
// Script by Javabitpro
// www.javabutpro.com
function doPost(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = JSON.parse(e.postData.contents);
  var dataArray = data.data;
// Script by Javabitpro
// www.javabutpro.com
  // Mengupdate data pada Google Sheets
  for (var i = 0; i < dataArray.length; i++) {
    var row = dataArray[i].row;
    var col = dataArray[i].col;
    var value = dataArray[i].value;

    sheet.getRange(row, col).setValue(value);
  }

  // Mengembalikan respons untuk menunjukkan bahwa data telah terkirim
  return ContentService.createTextOutput("Data telah terkirim").setMimeType(ContentService.MimeType.TEXT);
}

5. Klik ikon Save.

6. Klik tombol Terapkan/Deploy dan pilih Deployment baru/New Deployment.

7. Pastikan jenisnya adalah Aplikasi web, hak aksesnya adalah Siapa saja/Anyone lalu klik Terapkan/Deploy.

8. Setelah di Delploy akan menghasilkan URL yang siap disalin untuk di koneksikan dengan Excel.

9. Sekarang buka Ms. Excel 2010.

10. Klik menu Developer lalu pilih Visual Basic atau bisa dengan tekan tombol Alt+F11 pada keyobard, maka akan muncul lembar kerja Microsoft Visual Basic for Application (VBA).

11. Pada VBA klik Insert pada toolbar lalu pilih Module. Lakukan sebanyak 2x, sehingga terdapat 2 modul yang siap di isi script yaitu Module 1 dan Module 2.

12. Buka Module 1, lalu copy script di bawah ini ke Module 1.

*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*

Masukkan Password Untuk Melihat Script (Password sama dengan di atas)

' VBA (di Excel)
Sub KirimSemuaDataKeGoogleSheets()
    Dim URL As String
    Dim HTTPReq As Object
    Dim postData As String
    Dim responseText As String
    Dim dataRange As Range
    Dim cell As Range
    
    ' Sesuaikan URL dengan URL dari Google Apps Script Anda
    URL = "URL_GOOGLE_SCRIPT_APPS_YANG_SUDAH_DI_DEPLOY_TADI"
    
    ' Sesuaikan range yang ingin Anda kirim
    Set dataRange = Worksheets("Sheet1").Range("A1:H100")
    
    ' Membuat string data untuk dikirim
    postData = "{""data"": ["
    
    ' Iterasi melalui setiap sel dalam range dan menambahkannya ke string postData
    For Each cell In dataRange
        postData = postData & "{""row"": " & cell.row & ", ""col"": " & cell.column & ", ""value"": """ & cell.value & """},"
    Next cell
    
    ' Menghapus koma ekstra di akhir string postData
    postData = Left(postData, Len(postData) - 1)
    
    ' Menutup string postData
    postData = postData & "]}"
    
    ' Membuat objek XMLHTTP
    Set HTTPReq = CreateObject("MSXML2.ServerXMLHTTP")
    
    ' Mengirim data ke Google Sheets
    With HTTPReq
        .Open "POST", URL, False
        .setRequestHeader "Content-Type", "application/json"
        .send postData
        responseText = .responseText
    End With
    
    ' Menampilkan popup
    MsgBox "Data telah dikirim", vbInformation, "Javabitpro.com"
End Sub

Pada URL = "URL_GOOGLE_SCRIPT_APPS_YANG_SUDAH_DI_DEPLOY_TADI"
Silahkan ganti Url tersebut dengan Url dari Google Script App yang tadi sudah di Deploy.

13. Buka Module 2, lalu copy script di bawah ini ke Module 2.

Masukkan Password Untuk Melihat Script (Password sama dengan di atas)

' VBA (di Excel 2010) dengan pustaka JSON VBA
Sub AmbilDataDariGoogleSheets()
    Dim URL As String
    Dim HTTPReq As Object
    Dim responseText As String
    Dim json As Object
    Dim dataRows As Collection
    Dim i As Integer
    Dim j As Integer

    ' Sesuaikan URL dengan URL dari Google Apps Script Anda
    URL = "URL_GOOGLE_SCRIPT_APPS_YANG_SUDAH_DI_DEPLOY_TADI" ' Ganti dengan URL proyek Anda

    ' Membuat objek XMLHTTP
    Set HTTPReq = CreateObject("MSXML2.ServerXMLHTTP")

    ' Mengambil data dari Google Sheets
    With HTTPReq
        .Open "GET", URL, False
        .Send
        responseText = .responseText
    End With

    ' Mengonversi teks JSON menjadi objek
    Set json = JsonConverter.ParseJson(responseText)

    ' Menampilkan data di Excel, dimulai dari sel A1
    Set dataRows = New Collection
    If TypeName(json) = "Collection" Then
        ' Jika json adalah koleksi
        For Each Row In json
            dataRows.Add Row
        Next Row
    Else
        ' Jika json bukan koleksi
        MsgBox "Data yang diterima tidak dalam format yang diharapkan.", vbExclamation, "Peringatan"
    End If

    ' Menampilkan data dua dimensi ke dalam sel A1:C10
    i = 1
    For Each Row In dataRows
        j = 1
        For Each Item In Row
            Cells(i, j).Value = Item
            j = j + 1
        Next Item
        i = i + 1
    Next Row

    ' Menampilkan popup
    MsgBox "Data telah diambil", vbInformation, "Javabitpro.com"
End Sub

Pada URL = "URL_GOOGLE_SCRIPT_APPS_YANG_SUDAH_DI_DEPLOY_TADI"
Silahkan ganti Url tersebut dengan Url dari Google Script App yang tadi sudah di Deploy.

14. Download JsonConverter.bas untuk mengkonversi kalimat JSON String menjadi sebuah Objek yang dapat di tampilkan dari spreadsheet ke dalam Excel. (Klik Disini)

15. Klik menu File pada VBA, lalu pilih Import File. Cari file JsonConverter.bas yang sudah di download. Sehingga pada Folder Module terdapat 3 file.