google sheet te QUERY formülünü scripte çevirme ...

tamer42

Destek Ekibi
Destek Ekibi
Katılım
11 Mart 2005
Mesajlar
3,121
Excel Vers. ve Dili
Office 2013 İngilizce
Kıymetli Hocalarım merhaba;

elimizdeki mevcut google Sheet' te başka google Sheet dosyalarından veri çekiyoruz, veri çekmiş olduğumuz google tabloları standart yapıda ...

Mevcut google tablo "Data" sayfası içerisinde "A2" hücresine aşağıdaki formül ile kaynak tablolardan verileri çekebiliyoruz.

=QUERY({IMPORTRANGE(Ahmet,"Siparis!A2:F");IMPORTRANGE(Mehmet,"Siparis!A2:F")},"Select * Where Col1 is Not Null")

Burada "Ahmet" ve "Mehmet" kaynak google tabloların adresi , bu adreslerde Parametre sayfasının "A" sütununda yazılı;

buraya kadar bir sorun yok;

sorun şöyle kaynak dosyaların sayısı sürekli artmakta ve formül içine sürekli bu kaynak dosyaların adresini yazmak zor olacak,

bu veri çekme işlemini script koduyla yapabilir miyiz?

parametre sayfasında "A" sütununda yazılı olan adreslere gidip "Siparis" sayfasındaki verileri alt-alta çekecek

Kod:
var ss = SpreadsheetApp.getActive();

  var paramSheet = ss.getSheetByName('Parametre');
  var targetSheet = ss.getSheetByName('Data');

  const arrData = paramSheet.getRange('A:A').getValues();
  var lastA = arrData.map(x => x[0]).indexOf('') + 1;
yardımlarınız için şimdiden teşekkürler,
iyi akşamlar.
 
Katılım
11 Temmuz 2024
Mesajlar
241
Excel Vers. ve Dili
Excel 2021 Türkçe
Merhaba hocam, şu şekilde olması işinize yarar mı acaba deneyebilir misiniz;

Kod:
function cokluKaynaktanVeriCek() {
  var ss = SpreadsheetApp.getActive();
  var paramSheet = ss.getSheetByName('Parametre');
  var targetSheet = ss.getSheetByName('Data');

  const arrData = paramSheet.getRange('A:A').getValues();
  var lastA = arrData.map(x => x[0]).indexOf('');
  if (lastA === -1) lastA = arrData.length;
 
  var tumVeriler = [];
  for (var i = 0; i < lastA; i++) {
    var kaynakID = arrData[i][0];
    if (!kaynakID) continue;
    if (typeof kaynakID === 'string' && kaynakID.includes('https://')) {
      try {
        var eslesme = kaynakID.match(/[-\w]{25,}/);
        if (eslesme) {
          kaynakID = eslesme[0];
        }
      } catch (e) {
        Logger.log('URL\'den ID çıkarılırken hata: ' + kaynakID);
        continue;
      }
    }
    try {
      var kaynakTablo = SpreadsheetApp.openById(kaynakID);
      var kaynakSayfa = kaynakTablo.getSheetByName('Siparis');
      if (!kaynakSayfa) {
        Logger.log('Tabloda "Siparis" sayfası bulunamadı: ' + kaynakID);
        continue;
      }
      var sonSatir = kaynakSayfa.getLastRow();
      if (sonSatir < 2) continue; // Veri yoksa atla
      var kaynakVeriler = kaynakSayfa.getRange('A2:F' + sonSatir).getValues();
      kaynakVeriler.forEach(function(satir) {
        if (satir[0] !== "") {
          tumVeriler.push(satir);
        }
      });
      Logger.log(kaynakID + ' kaynağından ' + kaynakVeriler.length + ' satır veri çekildi.');
    } catch (hata) {
      Logger.log('Veri çekerken hata: ' + kaynakID + ': ' + hata);
    }
  }
  if (targetSheet.getLastRow() > 1) {
    targetSheet.getRange('A2:F' + targetSheet.getLastRow()).clearContent();
  }
  if (tumVeriler.length > 0) {
    targetSheet.getRange('A2:F' + (tumVeriler.length + 1)).setValues(tumVeriler);
    Logger.log('Toplam ' + tumVeriler.length + ' satır veri "Data" sayfasına yerleştirildi.');
  } else {
    Logger.log('Hiç veri bulunamadı veya çekilemedi.');
  }
}
 

tamer42

Destek Ekibi
Destek Ekibi
Katılım
11 Mart 2005
Mesajlar
3,121
Excel Vers. ve Dili
Office 2013 İngilizce
Merhaba hocam, şu şekilde olması işinize yarar mı acaba deneyebilir misiniz;

Kod:
function cokluKaynaktanVeriCek() {
  var ss = SpreadsheetApp.getActive();
  var paramSheet = ss.getSheetByName('Parametre');
  var targetSheet = ss.getSheetByName('Data');

  const arrData = paramSheet.getRange('A:A').getValues();
  var lastA = arrData.map(x => x[0]).indexOf('');
  if (lastA === -1) lastA = arrData.length;

  var tumVeriler = [];
  for (var i = 0; i < lastA; i++) {
    var kaynakID = arrData[i][0];
    if (!kaynakID) continue;
    if (typeof kaynakID === 'string' && kaynakID.includes('https://')) {
      try {
        var eslesme = kaynakID.match(/[-\w]{25,}/);
        if (eslesme) {
          kaynakID = eslesme[0];
        }
      } catch (e) {
        Logger.log('URL\'den ID çıkarılırken hata: ' + kaynakID);
        continue;
      }
    }
    try {
      var kaynakTablo = SpreadsheetApp.openById(kaynakID);
      var kaynakSayfa = kaynakTablo.getSheetByName('Siparis');
      if (!kaynakSayfa) {
        Logger.log('Tabloda "Siparis" sayfası bulunamadı: ' + kaynakID);
        continue;
      }
      var sonSatir = kaynakSayfa.getLastRow();
      if (sonSatir < 2) continue; // Veri yoksa atla
      var kaynakVeriler = kaynakSayfa.getRange('A2:F' + sonSatir).getValues();
      kaynakVeriler.forEach(function(satir) {
        if (satir[0] !== "") {
          tumVeriler.push(satir);
        }
      });
      Logger.log(kaynakID + ' kaynağından ' + kaynakVeriler.length + ' satır veri çekildi.');
    } catch (hata) {
      Logger.log('Veri çekerken hata: ' + kaynakID + ': ' + hata);
    }
  }
  if (targetSheet.getLastRow() > 1) {
    targetSheet.getRange('A2:F' + targetSheet.getLastRow()).clearContent();
  }
  if (tumVeriler.length > 0) {
    targetSheet.getRange('A2:F' + (tumVeriler.length + 1)).setValues(tumVeriler);
    Logger.log('Toplam ' + tumVeriler.length + ' satır veri "Data" sayfasına yerleştirildi.');
  } else {
    Logger.log('Hiç veri bulunamadı veya çekilemedi.');
  }
}
Hocam teşekkürler,
bu şekilde script ile verileri almak çok uzun sürüyor.

bu formül çok hızlı aslında;
=QUERY({IMPORTRANGE(Ahmet,"Siparis!A2:F");IMPORTRANGE(Mehmet,"Siparis!A2:F")},"Select * Where Col1 is Not Null")

parametre sayfası güncellendiği zaman;
script ile bu formülü oluşturmak ve "Data" sayfası içerisinde "A2" hücresine uygulamak çok pratik olacaktır.

iyi akşamlar.
 
Katılım
11 Temmuz 2024
Mesajlar
241
Excel Vers. ve Dili
Excel 2021 Türkçe
Formül ile böyle düzenleme yapınca hız açısında yeterli oluyor mu peki?

Kod:
function dinamikFormulOlustur() {
  var ss = SpreadsheetApp.getActive();
  var paramSheet = ss.getSheetByName('Parametre');
  var targetSheet = ss.getSheetByName('Data');
  const arrData = paramSheet.getRange('A:A').getValues();
  var lastA = arrData.findIndex(row => row[0] === "");
  if (lastA === -1) lastA = arrData.length;
  var importRangeParcalari = [];
  for (var i = 0; i < lastA; i++) {
    var kaynakID = arrData[i][0];
    if (!kaynakID) continue;
    if (typeof kaynakID === 'string' && kaynakID.includes('https://')) {
      try {
        var eslesme = kaynakID.match(/[-\w]{25,}/);
        if (eslesme) {
          kaynakID = eslesme[0];
        }
      } catch (e) {
        Logger.log('URL\'den ID çıkarılırken hata: ' + kaynakID);
        continue;
      }
    }
    importRangeParcalari.push('IMPORTRANGE("' + kaynakID + '","Siparis!A2:F")');
  }
  if (importRangeParcalari.length === 0) {
    Logger.log('Hiç geçerli kaynak bulunamadı. Formül oluşturulamadı.');
    return;
  }
  var formul = '=QUERY({' + importRangeParcalari.join(';') + '},"Select * Where Col1 is Not Null")';
  targetSheet.getRange('A2').setFormula(formul);
 
  Logger.log('Dinamik formül oluşturuldu ve yerleştirildi.');
}
 

tamer42

Destek Ekibi
Destek Ekibi
Katılım
11 Mart 2005
Mesajlar
3,121
Excel Vers. ve Dili
Office 2013 İngilizce
Formül ile böyle düzenleme yapınca hız açısında yeterli oluyor mu peki?

Kod:
function dinamikFormulOlustur() {
  var ss = SpreadsheetApp.getActive();
  var paramSheet = ss.getSheetByName('Parametre');
  var targetSheet = ss.getSheetByName('Data');
  const arrData = paramSheet.getRange('A:A').getValues();
  var lastA = arrData.findIndex(row => row[0] === "");
  if (lastA === -1) lastA = arrData.length;
  var importRangeParcalari = [];
  for (var i = 0; i < lastA; i++) {
    var kaynakID = arrData[i][0];
    if (!kaynakID) continue;
    if (typeof kaynakID === 'string' && kaynakID.includes('https://')) {
      try {
        var eslesme = kaynakID.match(/[-\w]{25,}/);
        if (eslesme) {
          kaynakID = eslesme[0];
        }
      } catch (e) {
        Logger.log('URL\'den ID çıkarılırken hata: ' + kaynakID);
        continue;
      }
    }
    importRangeParcalari.push('IMPORTRANGE("' + kaynakID + '","Siparis!A2:F")');
  }
  if (importRangeParcalari.length === 0) {
    Logger.log('Hiç geçerli kaynak bulunamadı. Formül oluşturulamadı.');
    return;
  }
  var formul = '=QUERY({' + importRangeParcalari.join(';') + '},"Select * Where Col1 is Not Null")';
  targetSheet.getRange('A2').setFormula(formul);

  Logger.log('Dinamik formül oluşturuldu ve yerleştirildi.');
}
Formül daha hızlı oluyor Hocam
çok teşekkürler,
bu arada bir şey daha sorabilirmiyim,
Parametre sayfasının "A" sütununda kaynak dosyaların adresi yazılı ya;
bu adresler formül ile çok uzun oluyor, bunları ad tanımlama yaparak kısaltmak , örneğin B sütununa Kısa adları yazsak "Ahmet" ve "Mehmet" gibi sonrada "A" sütununda yazan linkleri, "B" sütununda yazan kısa adlara göre ad tanımlama yapabilir miyiz? Bunu script ile yapmamız gerekecek
tekrar teşekkürler,
iyi akşamlar.
 
Katılım
11 Temmuz 2024
Mesajlar
241
Excel Vers. ve Dili
Excel 2021 Türkçe
Kod:
function adlandirmaVeDinamikFormulOlustur() {
  var ss = SpreadsheetApp.getActive();
  var paramSheet = ss.getSheetByName('Parametre');
  var targetSheet = ss.getSheetByName('Data');
 
  var namedRanges = ss.getNamedRanges();
  for (var i = 0; i < namedRanges.length; i++) {
    namedRanges[i].remove();
  }
  const dataRange = paramSheet.getRange('A1:B').getValues();
  var importRangeParcalari = [];
  var kaynaklarAciklama = "Kullanılan Kaynaklar:\n";
  for (var i = 0; i < dataRange.length; i++) {
    var kaynakID = dataRange[i][0];
    var kisaAd = dataRange[i][1]; 
    if (!kaynakID) continue;
 
    if (typeof kaynakID === 'string' && kaynakID.includes('https://')) {
      try {
        var eslesme = kaynakID.match(/[-\w]{25,}/);
        if (eslesme) {
          kaynakID = eslesme[0];
        }
      } catch (e) {
        Logger.log('URL\'den ID çıkarılırken hata: ' + kaynakID);
        continue;
      }
    }
    if (kisaAd) {
      var range = paramSheet.getRange(i+1, 1);
      try {
        ss.setNamedRange(kisaAd, range);
        kaynaklarAciklama += "- " + kisaAd + ": " + kaynakID + "\n";
        importRangeParcalari.push('IMPORTRANGE(INDIRECT("' + kisaAd + '"),"Siparis!A2:F")');
      } catch (e) {
        Logger.log('Adlandırılmış aralık oluşturulurken hata: ' + e.message);
        importRangeParcalari.push('IMPORTRANGE("' + kaynakID + '","Siparis!A2:F")');
      }
    } else {
      importRangeParcalari.push('IMPORTRANGE("' + kaynakID + '","Siparis!A2:F")');
    }
  }
 
  if (importRangeParcalari.length === 0) {
    Logger.log('Hiç geçerli kaynak bulunamadı. Formül oluşturulamadı.');
    return;
  }
  var formul = '=QUERY({' + importRangeParcalari.join(';') + '},"Select * Where Col1 is Not Null")';
  targetSheet.getRange('A2').setFormula(formul);
 
  var notCell = paramSheet.getRange('D1');
  notCell.setValue(kaynaklarAciklama);
 
  Logger.log('Dinamik formül ve adlandırılmış aralıklar oluşturuldu.');
}
Bu şekilde, daha sonra formülü manuel olarak düzenlerken, IMPORTRANGE içindeki dosya kimliklerini adlandırılmış aralıklarla değiştirebilirsiniz. Örneğin:
Orijinal: IMPORTRANGE("uzun-dosya-kimliği-123","Siparis!A2:F")
Değiştirilmiş: IMPORTRANGE(Ahmet,"Siparis!A2:F")
 

tamer42

Destek Ekibi
Destek Ekibi
Katılım
11 Mart 2005
Mesajlar
3,121
Excel Vers. ve Dili
Office 2013 İngilizce
Kod:
function adlandirmaVeDinamikFormulOlustur() {
  var ss = SpreadsheetApp.getActive();
  var paramSheet = ss.getSheetByName('Parametre');
  var targetSheet = ss.getSheetByName('Data');

  var namedRanges = ss.getNamedRanges();
  for (var i = 0; i < namedRanges.length; i++) {
    namedRanges[i].remove();
  }
  const dataRange = paramSheet.getRange('A1:B').getValues();
  var importRangeParcalari = [];
  var kaynaklarAciklama = "Kullanılan Kaynaklar:\n";
  for (var i = 0; i < dataRange.length; i++) {
    var kaynakID = dataRange[i][0];
    var kisaAd = dataRange[i][1];
    if (!kaynakID) continue;

    if (typeof kaynakID === 'string' && kaynakID.includes('https://')) {
      try {
        var eslesme = kaynakID.match(/[-\w]{25,}/);
        if (eslesme) {
          kaynakID = eslesme[0];
        }
      } catch (e) {
        Logger.log('URL\'den ID çıkarılırken hata: ' + kaynakID);
        continue;
      }
    }
    if (kisaAd) {
      var range = paramSheet.getRange(i+1, 1);
      try {
        ss.setNamedRange(kisaAd, range);
        kaynaklarAciklama += "- " + kisaAd + ": " + kaynakID + "\n";
        importRangeParcalari.push('IMPORTRANGE(INDIRECT("' + kisaAd + '"),"Siparis!A2:F")');
      } catch (e) {
        Logger.log('Adlandırılmış aralık oluşturulurken hata: ' + e.message);
        importRangeParcalari.push('IMPORTRANGE("' + kaynakID + '","Siparis!A2:F")');
      }
    } else {
      importRangeParcalari.push('IMPORTRANGE("' + kaynakID + '","Siparis!A2:F")');
    }
  }

  if (importRangeParcalari.length === 0) {
    Logger.log('Hiç geçerli kaynak bulunamadı. Formül oluşturulamadı.');
    return;
  }
  var formul = '=QUERY({' + importRangeParcalari.join(';') + '},"Select * Where Col1 is Not Null")';
  targetSheet.getRange('A2').setFormula(formul);

  var notCell = paramSheet.getRange('D1');
  notCell.setValue(kaynaklarAciklama);

  Logger.log('Dinamik formül ve adlandırılmış aralıklar oluşturuldu.');
}
Bu şekilde, daha sonra formülü manuel olarak düzenlerken, IMPORTRANGE içindeki dosya kimliklerini adlandırılmış aralıklarla değiştirebilirsiniz. Örneğin:
Orijinal: IMPORTRANGE("uzun-dosya-kimliği-123","Siparis!A2:F")
Değiştirilmiş: IMPORTRANGE(Ahmet,"Siparis!A2:F")
Hocam teşekkür ederim,
yalnız
"uzun-dosya-kimliği-123" >> string ifade
Ahmet >> Tanımlama (" ") işareti yok
bu şekilde sıkıntı olmaz mı?
iyi çalışmalar.
 
Katılım
6 Mart 2024
Mesajlar
250
Excel Vers. ve Dili
Excel 2010 TR & Excel 2016 TR
Merhaba,
sorun şöyle kaynak dosyaların sayısı sürekli artmakta ve formül içine sürekli bu kaynak dosyaların adresini yazmak zor olacak,

bu veri çekme işlemini script koduyla yapabilir miyiz?
Verilerin Çekileceği Sayfaların paylaşım adresleri A2:A hücrelerine yazılıyor ve
Formül ise Data Sayfasının C2 hücresine yazılacak Kabul ederek (benim örnek E-Tabloda)
Makro Kodları
JavaScript:
function FormulGuncelle() {

  var ss = SpreadsheetApp.getActive();
  var sayfa = ss.getSheetByName('Data');

  // Mevcud formülü temizle
  sayfa.getRange('C2').clearContent();

  var sonSatir = sayfa.getLastRow();
  var importRangeFormulleri = "";

  // 2. satırdan son dolu satıra kadar döngü, sayfa.getRange(i, 1) 1=A sütunu, getA1Notation()=Address
  for (var i = 2; i <= sonSatir; i++) {
    importRangeFormulleri += 'IMPORTRANGE(' + sayfa.getRange(i, 1).getA1Notation() + ';"Siparis!A2:F");'; // Hücre adresini kullan
  }

  // En Son fazladan yazılan karakteri ( ; ) sil
  importRangeFormulleri = importRangeFormulleri.slice(0, -1);

  var formul = '=QUERY({' + importRangeFormulleri + '}; "Select * Where Col1 is Not Null")';

  // C2 hücresine Formülü yaz
  sayfa.getRange('C2').setFormula(formul);
}
1. Seçenek Manuel Makro çalıştırmak
E-Tablo Menusünde​
Uzantılar > Makrolar > FormulGuncelle ile çalıştırabilirsiniz.​

2. Seçenek Otomatik Makro çalıştırmak
Makroda ek düzenleme yapıp ( hangi hücreler düzenlenince çalışacak)​
Tetikleyici eklemek lazım.​

Tetikleyici nasıl eklenir:
Apps Script bölümünde
Sol tarafta ki Çalar Saat Tetikleyiciler
+Tetikleyici Ekleyin


Çalışmasını istediğiniz işlevi seçin ( FormulAuto )

Etkinlik türünü seçin ( Düzenlendiğinde )

Artık A sütununda Değişiklik yatığınızda
Formül Otomatik Güncellenecektir

📂Örnek E-Tablo : SiparislerTotal (Google Sheets Apps li )
( E-Tablo Kopyalamada Tetikleyiciler Kopyalanmadığı için sizin eklemeniz gerekmekte )

@tamer42 sanırım sizin bölgesel ayarlarda liste ayırıcı , ( virgül ) sanırım
o yüzden Makro sonucunda oluşan formülde ; ( noktalı virgül ) den dolayı hata verecektir.
aynı problemi yaşayan arkadaşlar Makro kodlarını aşağıda ki kodlarda değişiklik yapmaları gerekmekte

her iki makroda ( FormulGuncelle() ve FormulAuto(e) ) bulunan kod parçalarını
JavaScript:
importRangeFormulleri += 'IMPORTRANGE(' + sayfa.getRange(i, 1).getA1Notation() + ';"Siparis!A2:F");'; // Hücre adresini kullan
// ve değiştirilmesi gereken diğer satır
var formul = '=QUERY({' + importRangeFormulleri + '}; "Select * Where Col1 is Not Null")';
aşağıda ki ile değiştirmeniz gerekmekte
JavaScript:
importRangeFormulleri += 'IMPORTRANGE(' + sayfa.getRange(i, 1).getA1Notation() + ',"Siparis!A2:F"),'; // Hücre adresini kullan
// ve diğer satır
var formul = '=QUERY({' + importRangeFormulleri + '}, "Select * Where Col1 is Not Null")';
 
Son düzenleme:
Üst