Google E-Tablo Bilgilerini Bilgisayarımızdaki Bir Excel Dosyasına Online Olarak Çekmek Mümkün mü?

[merrrakli]

Şu adreste Google E-Tablolar Üzerinden JSON Formatında Veri Çekme konusu anlatılmış.
Bu yapılabiliyorsa E-Tablolardaki bilgileri bilgisayarımızdaki bir excel dosyasına da anlık veri çekebiliriz düşüncesi oluştu.
Bu yapılabilir mi?
Ya da E-Tablolardaki bilgileri paylaşım linki üzerinden otomatik olarak almanın en kolay yolu nedir?

 

[hmtstc]

merhaba,

bu konuyla ilgili çözümü arkadaşımız paylaşmış

https://www.excel.web.tr/conversations/google-sheets-hk.152921/#convMessage-167264

ben denedim adımları uygularsanız yapabilirsiniz.

 

[Haluk]

Şu adreste Google E-Tablolar Üzerinden JSON Formatında Veri Çekme konusu anlatılmış.
Bu yapılabiliyorsa E-Tablolardaki bilgileri bilgisayarımızdaki bir excel dosyasına da anlık veri çekebiliriz düşüncesi oluştu.
Bu yapılabilir mi?
Ya da E-Tablolardaki bilgileri paylaşım linki üzerinden otomatik olarak almanın en kolay yolu nedir?

Paylaşıma açılmış örnek bir Google Sheet dosyasının linkini verirseniz, deneme yapalım ....

.

 

[Haluk]

Aşağıdaki gibi uyduruktan bir kodla veri alınabiliyor.... İyi, kötü idare eder ....

10 No'lu mesaj ekindeki dosyaya bakınız....

.

 

[wezyr]

Aşağıdaki gibi uyduruktan bir kodla veri alınabiliyor.... İyi, kötü idare eder ....

Sub Test()
'   Haluk - 15/04/2020
'   sa4truss@gmail.com
   
    myURL = "https://docs.google.com/spreadsheets/d/1uebxYNq-P97nRoEH2QeLPcYltZvwqKeK5pOvZIP0NBs/edit#gid=0"
   
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & myURL, Destination:=Range("$A$1"))
        .Name = "myTable"
'        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
       
        .RefreshPeriod = 0
       
        .WebSelectionType = xlSpecifiedTables
        .WebTables = 1
       
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
   
        Rows(1).Delete
        Columns(1).Delete
End Sub

.

ilk 101 satırı aldı tamamını almak için düzenlenirse güzel olur.

 

[Haluk]

ilk 101 satırı aldı tamamını almak için düzenlenirse güzel olur.

Bu sorun, koddan değil "Google Sheet"in kendisinden kaynaklanıyormuş.... (100 satırlık bir sınırlama varmış)

Bilgi ve çözüm için;

Load from google sheet limited to 100 rows

Hi I can get QlikView to load from an unrestricted Google Sheet OK, but it only seems to load the first 100 rows from the spreadsheet. Has anyone encountered this? A limitation with Google Sheets or QlikView? I've searched QlikCommunity and Google but no answers... Thanks Rory

community.qlik.com

Yukarıdaki linkte verilen, "Goggle Sheet" linkini kullanarak, 4. mesajdaki kodla söz konusu dosyadaki 114 satır alınmaktadır.


Google Sheet linki:

    myURL = "https://spreadsheets.google.com/tq?tqx=out:html&tq=&key=1wYmo3-D-R0H-BXl0hpNL0upJeX-Py3qkSxvpFRIeY6g&gid=1"

.

 

[merrrakli]

Aşağıdaki gibi uyduruktan bir kodla veri alınabiliyor.... İyi, kötü idare eder ....

Sub Test()
'   Haluk - 15/04/2020
'   sa4truss@gmail.com
   
    myURL = "https://docs.google.com/spreadsheets/d/1uebxYNq-P97nRoEH2QeLPcYltZvwqKeK5pOvZIP0NBs/edit#gid=0"
   
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & myURL, Destination:=Range("$A$1"))
        .Name = "myTable"
'        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
       
        .RefreshPeriod = 0
       
        .WebSelectionType = xlSpecifiedTables
        .WebTables = 1
       
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
   
        Rows(1).Delete
        Columns(1).Delete
End Sub

.

Teşekkür ederim.
Benim kafamdaki model şu şekildeydi.

 

[Haluk]

Aslında bahsettiğiniz modeli yapmak zor değil...... 4.mesajdaki kodla, Google Sheet'den tüm verileri lokal bilgisayarda Excel'de bir sayfaya aldıktan sonra artık, yine VBA kullanarak herhangibir yöntemle bu sayfadaki verileri modelinize göre 2 ayrı sayfaya ayrıştıracaksınız.

Verilerin sayfalara ayrıştırılma işi bu mesaj konusu Google Sheet olmadığı için, forumda ayrı bir konu başlığı açarak yardım talep edebilirsiniz...

.

 

[merrrakli]

Aslında bahsettiğiniz modeli yapmak zor değil...... 4.mesajdaki kodla, Google Sheet'den tüm verileri lokal bilgisayarda Excel'de bir sayfaya aldıktan sonra artık, yine VBA kullanarak herhangibir yöntemle bu sayfadaki verileri modelinize göre 2 ayrı sayfaya ayrıştıracaksınız.

Verilerin sayfalara ayrıştırılma işi bu mesaj konusu Google Sheet olmadığı için, forumda ayrı bir konu başlığı açarak yardım talep edebilirsiniz...

.

Henüz makro bilmiyorum.
Üstteki kodu örnek excel dosyamda çalışan şekliyle çalıştırabilirseniz burada bahsettiğiniz işlemleri yapmaya çalışırım inş.

 

[Haluk]

Ekli dosyayı deneyebilirsiniz...

.

 

[merrrakli]

Ekli dosyayı deneyebilirsiniz...

.

Teşekkür ederim

 

[Muhammet Okumuş]

Haluk Bey cevabınız için teşekkürler. Kod geçmiş veriler silinse bile yerleştirmeyi daha önce kaç veri girilmişse ondan devam ederek yapıyor. Bunu hücre boş ise satır sil ile yapabilirim. Peki kod veriyi A2'den başlayarak yerleştirebilir mi?

 

[merrrakli]

Ekli dosyayı deneyebilirsiniz...

.

@Haluk Bey şöyle bir sorunla karşılaştım.

Data sayfasından örneğin 8/A sınıfının öğrenci listesi olan başka bir sayfada öğrenci numarasına göre verileri çekip liste sırasına göre sıralatmak istiyorum.

=EĞERHATA(EĞER($B2<>"";İNDİS(Data!$F:$F;KAÇINCI($B2;Data!$E:$E;0));"");"")

şeklinde öğrencinin numarasına göre cevaplarını çekiyorum.
Burada problem yok.

Ancak Başka bir Google Linki yazıp Verileri Al button'a basınca sanırım Data sayfasını yeniden oluşturduğu için...

Benim liste sayfamdaki formüllerdeki Sayfa adı kısmı bozuluyor.

=EĞERHATA(EĞER($B2<>"";İNDİS(#BAŞV!$F:$F;KAÇINCI($B2;#BAŞV!$E:$E;0));"");"")

şekline dönüşüyor.

Bu sorunu nasıl aşabiliriz?

 

[Haluk]

Tespit ettiğiniz gibi, "Data" sayfası silindiği için, sayfadaki formüller bozulur.... O yüzden VLOOKUP-DÜŞEYARA formülünü kullanmaya gerek kalmaması için VBA ile Öğrenci Numarası'nı kullanarak 8A ve 8B tablolarını doldurmuştum.

Ben denemek üzere Google Sheet ve Excel dosyasındaki soru/cevap tablolarını 12 soruluk yaptım, VBA kodlarını hiç değiştirmedim ve kodlar gayet güzel çalıştı.

Ya da, ben sizin ne demek istediğinizi anlamıyorum...

.

 

[merrrakli]

Tespit ettiğiniz gibi, "Data" sayfası silindiği için, sayfadaki formüller bozulur.... O yüzden VLOOKUP-DÜŞEYARA formülünü kullanmaya gerek kalmaması için VBA ile Öğrenci Numarası'nı kullanarak 8A ve 8B tablolarını doldurmuştum.

Ben denemek üzere Google Sheet ve Excel dosyasındaki soru/cevap tablolarını 12 soruluk yaptım, VBA kodlarını hiç değiştirmedim ve kodlar gayet güzel çalıştı.

Ya da, ben sizin ne demek istediğinizi anlamıyorum...

.

Teşekkür ederim ben sadece verileri data dosyasına aldığı düşüncesine şartlanarak bakmıştım.
8A ve 8B sayfalarında ilgili alanlarda herhangi bir formül olmadığı için statik olarak düşünmüştüm.

Makro geçmişim yok ancak yıllar önce klasik ASP 3.0 bilgim olduğu için anlayabiliyorum. Ancak üzerine PHP öğrendiğim için bilgiler birbirine girdi yok sonuna virgül konacaktı konmayacaktı vb...

Hemen makro kodlarını inceliyorum.
kodu satır satır incelemek ve değişkenlerdeki değerleri görebilmek için o değişkeni alert() ile mi gözlemleyebiliyoruz. Console.log vb.
Formülü değerlendir durumu gibi...

örneğin ImportData() içindeki

NoData = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row

NoData içeriğini nasıl görebiliyorum

örneğin
klasik aspd Response. Write NoData
php de Echo NoData;
Javasripte Alert(NoData)
gibi...
Makroda bu verileri nasıl görebiliyoruz

Anladığım kadarı ile

GetData() ile verileri Goggle E-Tablodan çekiyoruz
ImportData() ile gelen verileri 8A ve 8B sayfalarındaki eşleşen numaralara verileri atıyoruz.


c xSh In Sheets
If xSh.Name <> "Data" Or xSh.Name <> "Giris" Then
NoA = Sheets(xSh.Name).Range("A" & Rows.Count).End(xlUp).Row
LastCol = Sheets(xSh.Name).Cells(2, Columns.Count).End(xlToLeft).Column
For iRow = 3 To NoA
For iRow2 = 2 To NoData
If Sheets("Data").Range("C" & iRow2) = Sheets(xSh.Name).Range("C" & iRow) Then
For j = 4 To LastCol
Sheets(xSh.Name).Cells(iRow, j) = Sheets("Data").Cells(iRow2, j)
Next
End If
Next
Next
End If
Next


çözmem gereken yer burası
ilk etapta for each döngüsüyle sayfa sayısı kadar döngü oluşturulmuş.
Sonrasında if else end if ile bu ayfalardan xSh.Name adı Data veya Giris olanlar seçilmiş
NoA = Sheets(xSh.Name).Range("A" & Rows.Count).End(xlUp).Row
LastCol = Sheets(xSh.Name).Cells(2, Columns.Count).End(xlToLeft).Column
Bunları tam olarak bilmiyorum satır ve sütun sayısı alıyor gibi
işte üstte sorduğum buydu NoA ve LastCol değişkenlerinin taşıdığı değerleri nasıl görebiliyorum.
For iRow = 3 To NoA ile For - Next döngüsünde 3. sütundan başlayıp son sütun numarasına kadar döngü oluşturuluyor.
For iRow2 = 2 To NoData ile For - Next döngüsünde 2. sütundan başlayıp numaraları almıyoruz.

Bana buraları kısaca açıklayabilir ve değişkenlerin değerlerini nasıl takip edebileceğimi söyleyebilirseniz sanırım bunu kendime göre düzenleyebilirim.


Teşekkürler

 

[Haluk]

Bence editörü biraz kurcalayın, orada bu işleri yapan araçlar var sanırım....

Locals window, watch window....

.

 

[Muhammet Okumuş]

Haluk Bey, Excel ile Google Form'daki yanıtları silmemiz mümkün mü?

 

[Haluk]

Haluk Bey, Excel ile Google Form'daki yanıtları silmemiz mümkün mü?

Gerçek anlamda silinir mi, bilmiyorum ama üzerine boş değer (NULL) yazılabilir belki ....

Google'ın bu işlerle ilgili Web API'leri var sanırım, onlarla uğraşmak lazım.

.

 

[merrrakli]

Tespit ettiğiniz gibi, "Data" sayfası silindiği için, sayfadaki formüller bozulur.... O yüzden VLOOKUP-DÜŞEYARA formülünü kullanmaya gerek kalmaması için VBA ile Öğrenci Numarası'nı kullanarak 8A ve 8B tablolarını doldurmuştum.

Ben denemek üzere Google Sheet ve Excel dosyasındaki soru/cevap tablolarını 12 soruluk yaptım, VBA kodlarını hiç değiştirmedim ve kodlar gayet güzel çalıştı.

Ya da, ben sizin ne demek istediğinizi anlamıyorum...

.

@Haluk bey üstteki örnek sayesinde artık google e-tablolara girmeden verileri çekip üzerinde uğraştığım dosyaya aktarıyorum.
e-tablolardaki hücrelere (A-B-C-D-Boş) şeklinde 5 tane sabit veriden bir tanesi kaydediliyor.
Bunları üstteki makroyla çekerken hücre değeri [Boş] değeri ile gelen verilerin değerini silmek için makroda nasıl bir değişiklik yapmalıyız

 

[Haluk]

.......
...
Bunları üstteki makroyla çekerken hücre değeri [Boş] değeri ile gelen verilerin değerini silmek için makroda nasıl bir değişiklik yapmalıyız

"ImportData" isimli prosedürü aşağıdaki şekliyle revize ettim;

Sub ImportData()
    Dim xSh As Worksheet, NoData As Byte, NoA As Byte, LastCol As Byte, iRow As Byte, iRow2 As Byte, j As Byte
    
    NoData = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    
    For Each xSh In Sheets
        If xSh.Name <> "Data" Or xSh.Name <> "Giris" Then
            NoA = Sheets(xSh.Name).Range("A" & Rows.Count).End(xlUp).Row
            LastCol = Sheets(xSh.Name).Cells(2, Columns.Count).End(xlToLeft).Column
            For iRow = 3 To NoA
                For iRow2 = 2 To NoData
                    If Sheets("Data").Range("C" & iRow2) = Sheets(xSh.Name).Range("C" & iRow) Then
                        For j = 4 To LastCol
                            tempData = Sheets("Data").Cells(iRow2, j)
                            Sheets(xSh.Name).Cells(iRow, j) = IIf(tempData = "Boş", "", tempData)
                        Next
                    End If
                Next
            Next
        End If
    Next
End Sub