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

[tamer42]

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

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.

 

[pitchoute]

Merhaba hocam, şu şekilde olması işinize yarar mı acaba deneyebilir misiniz;

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]

Merhaba hocam, şu şekilde olması işinize yarar mı acaba deneyebilir misiniz;

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.

 

[pitchoute]

Formül ile böyle düzenleme yapınca hız açısında yeterli oluyor mu peki?

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]

Formül ile böyle düzenleme yapınca hız açısında yeterli oluyor mu peki?

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.

 

[pitchoute]

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]

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.

 

[pitchoute]

Kodda ekleme yapılıyor hocam;

INDIRECT("' + kisaAd + '")