Google E-Tablolar ile Apps Komut Dosyası'nın Temelleri #3: Verilerle Çalışma

1. Giriş

Google E-Tablolar ile Apps Komut Dosyası'nın Temelleri adlı codelab oynatma listesinin üçüncü bölümüne hoş geldiniz.

Bu codelab'i tamamlayarak E-Tablolar deneyiminizi iyileştirmek için Apps Komut Dosyası'nda veri işleme, özel menüler ve herkese açık API veri alma işlemlerini nasıl kullanacağınızı öğrenebilirsiniz. Bu oynatma listesindeki önceki codelab'lerde tanıtılan SpreadsheetApp, Spreadsheet, Sheet ve Range sınıflarıyla çalışmaya devam edeceksiniz.

Neler öğreneceksiniz?

  • Drive'daki kişisel veya paylaşılan bir e-tablodan verileri içe aktarma
  • onOpen() işleviyle özel menü oluşturma
  • Google E-Tablolar hücrelerindeki dize veri değerlerini ayrıştırma ve değiştirme
  • Herkese açık bir API kaynağından JSON nesne verilerini çekme ve işleme

Başlamadan önce

Bu codelab, Google E-Tablolar ile Apps Komut Dosyası'nın Temelleri oynatma listesindeki üçüncü codelab'dir. Bu codelab'e başlamadan önce önceki codelab'leri tamamladığınızdan emin olun:

  1. Makrolar ve Özel İşlevler
  2. E-Tablolar, sayfalar ve aralıklar

İhtiyacınız olanlar

2. Kur

Bu codelab'deki alıştırmalar için çalışılacak bir e-tablo gerekir. Bu alıştırmalarda kullanacağınız bir e-tablo oluşturmak için aşağıdaki adımları uygulayın:

  1. Google Drive'ınızda bir e-tablo oluşturun. Bu işlemi, Drive arayüzünde Yeni > Google E-Tablolar'ı seçerek yapabilirsiniz. Yeni e-tablonuz oluşturulur ve açılır. Dosya, Drive klasörünüze kaydedilir.
  2. E-tablo başlığını tıklayın ve "Başlıksız e-tablo" olan başlığı "Veri Manipülasyonu ve Özel Menüler" olarak değiştirin. Sayfanız aşağıdaki gibi görünmelidir:

545c02912de7d112.png

  1. Komut dosyası düzenleyiciyi açmak için Uzantılar > Apps Komut Dosyası'nı tıklayın.
  2. Apps Komut Dosyası proje başlığını tıklayın ve "Untitled Project" (Adsız Proje) olan başlığı "Data Manipulation and Custom Menus" (Veri Manipülasyonu ve Özel Menüler) olarak değiştirin. Başlık değişikliğini kaydetmek için Yeniden adlandır'ı tıklayın.

Boş bir e-tablo ve proje ile laboratuvara başlamaya hazırsınız. Özel menüler hakkında bilgi edinmeye başlamak için sonraki bölüme geçin.

3. Genel bakış: Özel bir menü öğesiyle veri içe aktarma

Apps Komut Dosyası, Google E-Tablolar'da görünebilecek özel menüler tanımlamanıza olanak tanır. Google Dokümanlar, Google Slaytlar ve Google Formlar'da da özel menüleri kullanabilirsiniz. Özel bir menü öğesi tanımladığınızda bir metin etiketi oluşturur ve bunu komut dosyası projenizdeki bir Apps Komut Dosyası işlevine bağlarsınız. Ardından, menüyü kullanıcı arayüzüne ekleyerek Google E-Tablolar'da görünmesini sağlayabilirsiniz:

d6b694da6b8c6783.png

Kullanıcı özel bir menü öğesini tıkladığında, bu öğeyle ilişkilendirdiğiniz Apps Komut Dosyası işlevi yürütülür. Bu, komut dosyası düzenleyicisini açmadan Apps Komut Dosyası işlevlerini çalıştırmanın hızlı bir yoludur. Ayrıca, e-tablonun diğer kullanıcılarının, kodunuzun veya Apps Komut Dosyası'nın nasıl çalıştığı hakkında hiçbir şey bilmeden kodunuzu yürütmesine olanak tanır. Bu kullanıcılar için bu özellik yalnızca başka bir menü öğesidir.

Özel menü öğeleri, bir sonraki bölümde öğreneceğiniz onOpen() basit tetikleyici işlevinde tanımlanır.

4. onOpen() işlevi

Apps Komut Dosyası'ndaki basit tetikleyiciler, belirli koşullara veya etkinliklere yanıt olarak belirli Apps Komut Dosyası kodlarını çalıştırmanın bir yolunu sunar. Tetikleyici oluşturduğunuzda, tetikleyicinin etkinleşmesine neden olan etkinliği tanımlar ve etkinlik için çalıştırılan bir Apps Komut Dosyası işlevi sağlarsınız.

onOpen(), basit bir tetikleyici örneğidir. Bu işlevleri ayarlamak kolaydır. Tek yapmanız gereken onOpen() adlı bir Apps Komut Dosyası işlevi yazmaktır. Apps Komut Dosyası, ilişkili e-tablo her açıldığında veya yeniden yüklendiğinde bu işlevi çalıştırır:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
 /* ... */ 
}

Uygulama

Özel bir menü oluşturalım.

  1. Komut dosyası projenizdeki kodu aşağıdakilerle değiştirin:
/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addToUi();
}
  1. Senaryo projenizi kaydedin.

Kod incelemesi

İşleyiş şeklini anlamak için bu kodu inceleyelim. onOpen() içinde, ilk satır bu komut dosyasının bağlı olduğu etkin e-tablonun kullanıcı arayüzünü temsil eden bir Ui nesnesi edinmek için getUi() yöntemini kullanır.

Sonraki üç satırda menü oluşturulur (Book-list), bu menüye bir menü öğesi eklenir (Load Book-list) ve menü, e-tablonun arayüzüne eklenir. Bu işlem sırasıyla createMenu(caption), addItem(caption, functionName) ve addToUi() yöntemleriyle yapılır.

addItem(caption, functionName) yöntemi, menü öğesi etiketi ile menü öğesi seçildiğinde çalışan Apps Komut Dosyası işlevi arasında bağlantı oluşturur. Bu durumda, Load Book-list menü öğesinin seçilmesiyle E-Tablolar, loadBookList() işlevini (henüz mevcut olmayan) çalıştırmaya çalışır.

Sonuçlar

Çalıştığını görmek için bu işlevi hemen çalıştırın:

  1. Google E-Tablolar'da e-tablonuzu yeniden yükleyin. Not: Bu işlem genellikle komut dosyası düzenleyicinizin bulunduğu sekmeyi kapatır.
  2. Araçlar > Komut dosyası düzenleyici'yi seçerek komut dosyası düzenleyicinizi yeniden açın.

E-tablonuz yeniden yüklendikten sonra menü çubuğunuzda yeni Book-list menüsü görünür:

687dfb214f2930ba.png

Kitap listesi'ni tıkladığınızda aşağıdaki menü gösterilir:

8a4a391fbabcb16a.png

Sonraki bölümde loadBookList() işlevinin kodu oluşturulur ve Apps Komut Dosyası'ndaki verilerle etkileşim kurmanın bir yolu olan diğer e-tabloları okuma yöntemi tanıtılır.

5. E-tablo verilerini içe aktarma

Özel bir menü oluşturduğunuza göre artık menü öğesini tıklayarak çalıştırılabilecek işlevler oluşturabilirsiniz.

Şu anda, özel menü Book-list bir menü öğesi içeriyor: Load Book-list. Load Book-list menü öğesini seçtiğinizde çağrılan loadBookList(), işlevi, komut dosyanızda mevcut değil. Bu nedenle, Kitap listesi > Kitap listesini yükle'yi seçtiğinizde hata oluşuyor:

b94dcef066e7041d.gif

loadBookList() işlevini uygulayarak bu hatayı düzeltebilirsiniz.

Uygulama

Yeni menü öğesinin, üzerinde çalışılacak verilerle e-tabloyu doldurmasını istiyorsunuz. Bu nedenle, kitap verilerini başka bir e-tablodan okuyup bu e-tabloya kopyalamak için loadBookList() işlevini uygulayacaksınız:

  1. onOpen() bölümündeki komut dosyanıza aşağıdaki kodu ekleyin:
/** 
 * Creates a template book list based on the
 * provided 'codelab-book-list' sheet.
 */
function loadBookList(){
  // Gets the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Gets a different spreadsheet from Drive using
  // the spreadsheet's ID. 
  var bookSS = SpreadsheetApp.openById(
    "1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo" 
  );

  // Gets the sheet, data range, and values of the
  // spreadsheet stored in bookSS.
  var bookSheet = bookSS.getSheetByName("codelab-book-list");
  var bookRange = bookSheet.getDataRange();
  var bookListValues = bookRange.getValues();

  // Add those values to the active sheet in the current
  // spreadsheet. This overwrites any values already there.
  sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth()) 
    .setValues(bookListValues);
  
  // Rename the destination sheet and resize the data
  // columns for easier reading.
  sheet.setName("Book-list");
  sheet.autoResizeColumns(1, 3);
}
  1. Senaryo projenizi kaydedin.

Kod incelemesi

Peki bu işlev nasıl çalışır? loadBookList() işlevi, önceki codelab'lerde tanıtılan Spreadsheet, Sheet ve Range sınıflarındaki yöntemleri kullanır. Bu kavramları göz önünde bulundurarak loadBookList() kodunu aşağıdaki dört bölüme ayırabilirsiniz:

1. Hedef e-tabloyu belirleyin

İlk satır, geçerli sayfa nesnesine referans almak için SpreadsheetApp.getActiveSheet() işlevini kullanır ve bunu sheet değişkeninde saklar. Verilerin kopyalanacağı sayfa budur.

2: Kaynak verileri belirleyin

Sonraki birkaç satırda, aldığınız kaynak verileri ifade eden dört değişken oluşturulur:

  • bookSS, kodun veri okuduğu e-tablonun referansını depolar. Kod, e-tabloyu e-tablo kimliğine göre bulur. Bu örnekte, okunacak bir kaynak e-tablonun kimliğini sağladık ve SpreadsheetApp.openById(id) yöntemini kullanarak e-tabloyu açtık.
  • bookSheet, istediğiniz verileri içeren bookSS içindeki bir sayfaya referans depolar. Kod, okunacak sayfayı adıyla (codelab-book-list) tanımlar.
  • bookRange, bookSheet içindeki bir veri aralığına referans depolar. Sheet.getDataRange() yöntemi, sayfadaki tüm boş olmayan hücreleri içeren aralığı döndürür. Bu, boş satır ve sütunları dahil etmeden bir sayfadaki tüm verileri kapsayan bir aralık elde etmenizi sağlayan kolay bir yöntemdir.
  • bookListValues, bookRange içindeki hücrelerden alınan tüm değerleri içeren bir 2 boyutlu dizidir. Range.getValues() yöntemi, kaynak sayfadaki verileri okuyarak bu diziyi oluşturur.

3. Verileri kaynaktan hedefe kopyalayın

Sonraki kod bölümü, bookListValues verilerini sheet içine kopyalar ve ardından sayfayı yeniden adlandırır:

4. Hedef sayfayı biçimlendirin

Sheet.setName(name), hedef sayfa adını Book-list olarak değiştirmek için kullanılır. İşlevdeki son satır, hedef sayfadaki ilk üç sütunu yeniden boyutlandırmak için Sheet.autoResizeColumns(startColumn, numColumns) kullanır. Böylece yeni verileri daha kolay okuyabilirsiniz.

Sonuçlar

Bu işlevin nasıl çalıştığını görebilirsiniz. E-tablonuzu doldurmak için işlevi çalıştırmak üzere Google E-Tablolar'da Kitap listesi > Kitap listesini yükle'yi seçin:

3c797e1e2b9fe641.gif

Artık kitap başlıkları, yazarlar ve 13 haneli ISBN numaralarının listelendiği bir sayfanız var. Sonraki bölümde, dize işleme ve özel menüleri kullanarak bu kitap listesindeki verileri nasıl değiştireceğinizi ve güncelleyeceğinizi öğreneceksiniz.

6. Genel bakış: E-tablo verilerini temizleme

Artık e-tablonuzda kitap bilgileri var. Her satır belirli bir kitabı ifade eder ve başlığını, yazarını ve ISBN numarasını ayrı sütunlarda listeler. Ancak bu ham verilerde bazı sorunlar da görebilirsiniz:

  1. Bazı satırlarda başlık ve yazar, başlık sütununa birlikte yerleştirilir ve virgül veya " by " dizesiyle bağlanır.
  2. Bazı satırlarda kitabın başlığı veya yazarı eksik.

Sonraki bölümlerde, verileri temizleyerek bu sorunları düzelteceksiniz. İlk sorun için başlık sütununu okuyan ve virgül veya "by" ayırıcı bulunduğunda metni bölen, ilgili yazar ve başlık alt dizelerini doğru sütunlara yerleştiren işlevler oluşturacaksınız. İkinci sorun için, harici bir API kullanarak eksik kitap bilgilerini otomatik olarak arayan ve bu bilgileri sayfanıza ekleyen bir kod yazacaksınız.

7. Menü öğeleri ekleme

Uygulayacağınız veri temizleme işlemlerini kontrol etmek için üç menü öğesi oluşturmanız gerekir.

Uygulama

onOpen() öğesini, ihtiyacınız olacak ek menü öğelerini içerecek şekilde güncelleyelim. Aşağıdakileri yapın:

  1. Komut dosyası projenizde onOpen() kodunuzu aşağıdakiyle eşleşecek şekilde güncelleyin:
/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addSeparator()
    .addItem(
      'Separate title/author at first comma', 'splitAtFirstComma')
    .addItem(
      'Separate title/author at last "by"', 'splitAtLastBy')
    .addSeparator()
    .addItem(
      'Fill in blank titles and author cells', 'fillInTheBlanks')
    .addToUi();
}
  1. Senaryo projenizi kaydedin.
  2. Komut dosyası düzenleyicide, işlevler listesinden onOpen simgesini seçin ve Çalıştır'ı tıklayın. Bu işlem, e-tabloyu yeniden yüklemeniz gerekmemesi için e-tablo menüsünü yeniden oluşturmak üzere onOpen()'yı çalıştırır.

Bu yeni kodda, Menu.addSeparator() yöntemi, ilgili menü öğeleri gruplarını görsel olarak düzenli tutmak için menüde yatay bir ayırıcı oluşturur. Yeni menü öğeleri, Separate title/author at first comma, Separate title/author at last "by" ve Fill in blank titles and author cells etiketleriyle bu öğenin altına eklenir.

Sonuçlar

E-tablonuzda, yeni menü öğelerini görüntülemek için Book-list menüsünü tıklayın:

580c806ce8fd4872.png

Bu yeni öğeleri tıkladığınızda, ilgili işlevleri uygulamadığınız için hata oluşur. Bu nedenle, bir sonraki adımda bu işlevleri uygulayacağız.

8. Metni virgül sınırlayıcılarına göre bölme

E-tablonuza aktardığınız veri kümesinde, yazar ve başlığın virgülle tek bir hücrede yanlış şekilde birleştirildiği birkaç hücre var:

ca91c43c4e51d6b5.png

Metin dizelerini ayrı sütunlara bölmek, elektronik tablolarda sıkça yapılan bir işlemdir. Google E-Tablolar, dizeleri sütunlara bölen bir SPLIT() işlevi sunar. Ancak veri kümelerinde genellikle E-Tablolar'ın yerleşik işlevleriyle kolayca çözülemeyen sorunlar bulunur. Bu gibi durumlarda, verilerinizi temizlemek ve düzenlemek için gereken karmaşık işlemleri yapmak üzere Apps Komut Dosyası kodu yazabilirsiniz.

Öncelikle, virgül bulunduğunda yazar ve başlığı ilgili hücrelere ayıran splitAtFirstComma() adlı bir işlevi uygulayarak verilerinizi temizlemeye başlayın.

splitAtFirstComma() işlevi aşağıdaki adımları uygulamalıdır:

  1. Şu anda seçili hücreleri temsil eden aralığı alır.
  2. Aralıktaki hücrelerde virgül olup olmadığını kontrol edin.
  3. Virgül bulunan yerlerde dizeyi ilk virgülün bulunduğu konumda iki (ve yalnızca iki) alt dizeye bölün. İşleri kolaylaştırmak için herhangi bir virgülün "[yazarlar], [başlık]" dize kalıbını gösterdiğini varsayabilirsiniz. Hücrede birden fazla virgül görünüyorsa dizeyi ilk virgülden bölmenin uygun olduğunu da varsayabilirsiniz.
  4. Alt dizeleri, ilgili başlık ve yazar hücrelerinin yeni içerikleri olarak ayarlayın.

Uygulama

Bu adımları uygulamak için daha önce kullandığınız E-tablo hizmeti yöntemlerini kullanacaksınız ancak dize verilerini işlemek için JavaScript de kullanmanız gerekecek. Aşağıdaki adımları uygulayın:

  1. Apps Komut Dosyası Düzenleyici'de, komut dosyası projenizin sonuna aşağıdaki işlevi ekleyin:
/**
 * Reformats title and author columns by splitting the title column
 * at the first comma, if present.
 */
function splitAtFirstComma(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where commas are found. Assumes the presence
  // of a comma indicates an "authors, title" pattern.
  for (var row = 0; row < titleAuthorValues.length; row++){
    var indexOfFirstComma =
        titleAuthorValues[row][0].indexOf(", ");

    if(indexOfFirstComma >= 0){
      // Found a comma, so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];

      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(indexOfFirstComma + 2);

      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(0, indexOfFirstComma);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}
  1. Senaryo projenizi kaydedin.

Kod incelemesi

Üç ana bölümden oluşan yeni kodu inceleyelim:

1. Adım: Vurgulanan başlık değerlerini alın

İlk üç satır, sayfadaki mevcut verilere atıfta bulunan üç değişken oluşturur:

  • activeRange, splitAtFirstComma() işlevi çağrıldığında kullanıcının vurguladığı aralığı temsil eder. Bu alıştırmayı basit tutmak için kullanıcının bu işlemi yalnızca A sütunundaki hücreleri vurgularken yaptığını varsayabiliriz.
  • titleAuthorRange, activeRange ile aynı hücreleri kapsayan ancak sağda bir sütun daha içeren yeni bir aralığı temsil eder. titleAuthorRange, Range.offset(rowOffset, columnOffset, numRows, numColumns) yöntemi kullanılarak oluşturulur. Kodun, başlık sütununda bulduğu yazarları yerleştirebileceği bir yer olması gerektiğinden bu genişletilmiş aralığa ihtiyacı vardır.
  • titleAuthorValues, Range.getValues() kullanılarak titleAuthorRange kaynağından çıkarılan verilerin 2 boyutlu bir dizisidir.

2: Her başlığı inceleyin ve bulunan ilk virgül sınırlayıcısına göre bölün

Bir sonraki bölümde, virgül bulmak için titleAuthorValues içindeki değerler incelenir. titleAuthorValues'ın ilk sütunundaki tüm değerleri incelemek için JavaScript For Döngüsü kullanılır. JavaScript String indexOf() yöntemi kullanılarak virgül alt dizesi (", ") bulunduğunda kod şunları yapar:

  1. Hücre dizesi değeri, titlesAndAuthors değişkenine kopyalanır.
  2. Virgülün konumu, JavaScript String indexOf() yöntemi kullanılarak belirlenir.
  3. Virgül sınırlayıcıdan önceki ve sonraki alt dizeleri elde etmek için JavaScript String slice() yöntemi iki kez çağrılır.
  4. Alt dizeler, titleAuthorValues 2D dizisine geri kopyalanır ve bu konumdaki mevcut değerlerin üzerine yazılır. "[yazarlar], [başlık]" kalıbını kullandığımızı varsaydığımız için başlığı ilk sütuna, yazarları ise ikinci sütuna yerleştirmek üzere iki alt dizenin sırası tersine çevrilir.

Not: Kod virgül bulamadığında satırdaki verileri değiştirmeden bırakır.

3: Yeni değerleri tekrar sayfaya kopyalayın

Başlık hücresi değerlerinin tümü incelendikten sonra, güncellenen titleAuthorValues 2D dizisi Range.setValues(values) yöntemi kullanılarak e-tabloya geri kopyalanır.

Sonuçlar

Artık splitAtFirstComma() işlevinin etkilerini görebilirsiniz. Aşağıdakileri seçtikten sonra İlk virgülün ardından başlığı/yazarı ayır menü öğesini belirleyerek çalıştırmayı deneyin:

...bir hücre:

a24763b60b305376.gif

...veya birden fazla hücre:

89c5c89b357d3713.gif

Artık E-Tablolar verilerini işleyen bir Apps Komut Dosyası işlevi oluşturdunuz. Ardından, ikinci ayırıcı işlevi uygulayacaksınız.

9. Metni "by" sınırlayıcılarına göre bölme

Orijinal verilere baktığınızda başka bir sorun olduğunu görebilirsiniz. Bazı veri biçimleri başlıkları ve yazarları tek bir hücrede "[yazarlar], [başlık]" olarak biçimlendirirken diğer hücreler yazar ve başlığı "[yazarlar] tarafından [başlık]" olarak biçimlendirir:

41f0dd5ac63b62f4.png

Uygulama

Bu sorunu, son bölümdeki aynı tekniği kullanarak çözebilirsiniz. Bunun için splitAtLastBy() adlı bir işlev oluşturun. Bu işlev, splitAtFirstComma() ile benzer bir işe sahiptir. Aralarındaki tek gerçek fark, biraz farklı bir metin kalıbı araması yapmasıdır. Aşağıdaki adımları uygulayarak bu işlevi kullanıma sunun:

  1. Apps Komut Dosyası Düzenleyici'de, komut dosyası projenizin sonuna aşağıdaki işlevi ekleyin:
/** 
 * Reformats title and author columns by splitting the title column
 * at the last instance of the string " by ", if present.
 */
function splitAtLastBy(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where " by " substrings are found. Assumes
  // the presence of a " by " indicates a "title by authors"
  // pattern.
  for(var row = 0; row < titleAuthorValues.length; row++){
    var indexOfLastBy =
        titleAuthorValues[row][0].lastIndexOf(" by ");
    
    if(indexOfLastBy >= 0){
      // Found a " by ", so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];
      
      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);
      
      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 4);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}
  1. Senaryo projenizi kaydedin.

Kod incelemesi

Bu kod ile splitAtFirstComma() arasında birkaç temel fark vardır:

  1. ", " yerine dize sınırlayıcı olarak " by " alt dizesi kullanılır.
  2. Burada String.indexOf(substring) yerine JavaScript String.lastIndexOf(substring) yöntemi kullanılıyor. Bu, başlangıç dizesinde birden fazla " by " alt dizesi varsa son " by " dışındaki tüm alt dizelerin başlığın bir parçası olarak kabul edileceği anlamına gelir.
  3. Dize bölündükten sonra ilk alt dize başlık, ikinci alt dize ise yazar olarak ayarlanır (bu, splitAtFirstComma() ile ters sıradadır).

Sonuçlar

Artık splitAtLastBy() işlevinin etkilerini görebilirsiniz. Aşağıdakileri seçtikten sonra Son "by" ifadesinde başlığı/yazarı ayır menü öğesini belirleyerek çalıştırmayı deneyin.

...bir hücre:

4e6679e134145975.gif

...veya birden fazla hücre:

3c879c572c61e62f.gif

Codelab'in bu bölümünü tamamladınız. Artık bir sayfadaki dize verilerini okumak ve değiştirmek için Apps Komut Dosyası'nı kullanabilir, farklı Apps Komut Dosyası komutlarını yürütmek için özel menülerden yararlanabilirsiniz.

Sonraki bölümde, boş hücreleri herkese açık bir API'den alınan verilerle doldurarak bu veri kümesini nasıl daha da iyileştireceğinizi öğreneceksiniz.

10. Genel bakış: Herkese açık API'lerden veri alma

Şimdiye kadar veri kümenizi, başlık ve yazar biçimlendirmesiyle ilgili bazı sorunları düzeltecek şekilde iyileştirdiniz. Ancak veri kümesinde, aşağıdaki hücrelerde vurgulanan bazı bilgiler hâlâ eksik.

af0dba8cb09d1a49.png

Şu anda sahip olduğunuz verilerde dize işlemleri yaparak eksik verileri elde edemezsiniz. Bunun yerine, eksik verileri başka bir kaynaktan almanız gerekir. Bunu, ek veriler sağlayabilen harici API'lerden bilgi isteyerek Apps Komut Dosyası'nda yapabilirsiniz.

API'ler, uygulama programlama arayüzleridir. Bu genel bir terimdir ancak temelde programlarınızın ve komut dosyalarınızın bilgi istemek veya belirli işlemleri gerçekleştirmek için çağırabileceği bir hizmettir. Bu bölümde, e-tablonuzdaki boş hücrelere ekleyebileceğiniz kitap bilgilerini istemek için herkese açık bir API çağırıyorsunuz.

Bu bölümde şunları nasıl yapacağınız açıklanmaktadır:

  • Harici bir API kaynağından kitap verileri isteyin.
  • Döndürülen verilerden başlık ve yazar bilgilerini ayıklayıp e-tablonuza yazın.

11. UrlFetch ile harici verileri getirme

Doğrudan e-tablonuzla çalışan kodlara geçmeden önce, herkese açık Open Library API'den kitap bilgisi istemek için özel olarak tasarlanmış bir yardımcı işlev oluşturarak Apps Script'te harici API'lerle çalışma hakkında bilgi edinebilirsiniz.

Yardımcı işlevimiz fetchBookData_(ISBN), parametre olarak bir kitabın 13 haneli ISBN numarasını alır ve bu kitapla ilgili verileri döndürür. Open Library API'sine bağlanıp buradan bilgi alır ve döndürülen JSON nesnesini ayrıştırır.

Uygulama

Aşağıdaki adımları uygulayarak bu yardımcı işlevi kullanıma sunun:

  1. Apps Komut Dosyası düzenleyicide aşağıdaki kodu komut dosyanızın sonuna ekleyin:
/**
 * Helper function to retrieve book data from the Open Library
 * public API.
 *
 * @param {number} ISBN - The ISBN number of the book to find.
 * @return {object} The book's data, in JSON format.
 */
function fetchBookData_(ISBN){
  // Connect to the public API.
  var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
      + ISBN + "&jscmd=details&format=json";
  var response = UrlFetchApp.fetch(
      url, {'muteHttpExceptions': true});
  
  // Make request to API and get response before this point.
  var json = response.getContentText();
  var bookData = JSON.parse(json); 
  
  // Return only the data we're interested in.
  return bookData['ISBN:' + ISBN];
}
  1. Senaryo projenizi kaydedin.

Kod incelemesi

Bu kod iki ana bölüme ayrılır:

1: API isteği

İlk iki satırda fetchBookData_(ISBN), API'nin URL uç noktasını ve Apps Komut Dosyası'nın URL Fetch Service'ini kullanarak herkese açık Open Library API'ye bağlanır.

url değişkeni, web adresi gibi bir URL dizesidir. Open Library sunucularındaki bir konumu gösterir. Ayrıca, Open Library sunucularına hangi bilgileri istediğinizi ve yanıtın nasıl yapılandırılacağını bildiren üç parametre (bibkeys, jscmd ve format) içerir. Bu durumda, kitabın ISBN numarasını sağlarsınız ve ayrıntılı bilgilerin JSON biçiminde döndürülmesini istersiniz.

URL dizesini oluşturduktan sonra kod, konuma bir istek gönderir ve yanıt alır. Bu işlem, UrlFetchApp.fetch(url, params) yöntemiyle yapılır. Sağladığınız harici URL'ye bir bilgi isteği gönderir ve sonuçtaki yanıtı response değişkeninde saklar. Kod, URL'ye ek olarak isteğe bağlı muteHttpExceptions parametresini true olarak ayarlar. Bu ayar, istek bir API hatasıyla sonuçlanırsa kodunuzun durmayacağı anlamına gelir. Bunun yerine hata yanıtı döndürülür.

İstek, response değişkeninde depolanan bir HTTPResponse nesnesi döndürür. HTTP yanıtları; yanıt kodu, HTTP üst bilgileri ve ana yanıt içeriğini içerir. Burada ilgilenilen bilgi, ana JSON içeriğidir. Bu nedenle kodun bu bilgiyi ayıklaması, ardından istenen bilgiyi bulup döndürmek için JSON'u ayrıştırması gerekir.

2: API yanıtını ayrıştırın ve ilgilendiğiniz bilgileri döndürün

Kodun son üç satırında, HTTPResponse.getContentText() yöntemi, yanıtın ana içeriğini dize olarak döndürür. Bu dize JSON biçimindedir ancak Open Library API, içeriği ve biçimi tam olarak tanımlar. JSON.parse(jsonString) yöntemi, JSON dizesini bir JavaScript nesnesine dönüştürür. Böylece verilerin farklı bölümleri kolayca çıkarılabilir. Son olarak, işlev kitabın ISBN numarasına karşılık gelen verileri döndürür.

Sonuçlar

fetchBookData_(ISBN) işlevini uyguladığınıza göre, kodunuzdaki diğer işlevler ISBN numarasını kullanarak herhangi bir kitapla ilgili bilgi bulabilir. Bu işlevi, e-tablonuzdaki hücreleri doldurmak için kullanacaksınız.

12. API verilerini e-tabloya yazma

Artık aşağıdakileri yapan bir fillInTheBlanks() işlevi uygulayabilirsiniz:

  1. Etkin veri aralığındaki eksik başlık ve yazar verilerini belirleyin.
  2. fetchBookData_(ISBN) yardımcı yöntemini kullanarak Open Library API'yi çağırarak belirli bir kitabın eksik verilerini alın.
  3. Eksik başlık veya yazar değerlerini ilgili hücrelerde güncelleyin.

Uygulama

Bu yeni işlevi uygulamak için aşağıdakileri yapın:

  1. Apps Komut Dosyası Düzenleyici'de aşağıdaki kodu komut dosyası projenizin sonuna ekleyin:
/**
 * Fills in missing title and author data using Open Library API
 * calls.
 */ 
function fillInTheBlanks(){
  // Constants that identify the index of the title, author,
  // and ISBN columns (in the 2D bookValues array below). 
  var TITLE_COLUMN = 0;
  var AUTHOR_COLUMN = 1;
  var ISBN_COLUMN = 2;

  // Get the existing book information in the active sheet. The data
  // is placed into a 2D array.
  var dataRange = SpreadsheetApp.getActiveSpreadsheet()
    .getDataRange();
  var bookValues = dataRange.getValues();

  // Examine each row of the data (excluding the header row).
  // If an ISBN is present, and a title or author is missing,
  // use the fetchBookData_(isbn) method to retrieve the
  // missing data from the Open Library API. Fill in the
  // missing titles or authors when they're found.
  for(var row = 1; row < bookValues.length; row++){   
    var isbn = bookValues[row][ISBN_COLUMN];
    var title = bookValues[row][TITLE_COLUMN];
    var author = bookValues[row][AUTHOR_COLUMN];
   
    if(isbn != "" && (title === "" || author === "") ){
      // Only call the API if you have an ISBN number and
      // either the title or author is missing.
      var bookData = fetchBookData_(isbn);

      // Sometimes the API doesn't return the information needed.
      // In those cases, don't attempt to update the row.
      if (!bookData || !bookData.details) {
        continue;
      }

      // The API might not return a title, so only fill it in
      // if the response has one and if the title is blank in
      // the sheet.
      if(title === "" && bookData.details.title){
        bookValues[row][TITLE_COLUMN] = bookData.details.title; 
      }

      // The API might not return an author name, so only fill it in
      // if the response has one and if the author is blank in
      // the sheet.
      if(author === "" && bookData.details.authors
          && bookData.details.authors[0].name){
        bookValues[row][AUTHOR_COLUMN] =
          bookData.details.authors[0].name; 
      }
    }
  }
  
  // Insert the updated book data values into the spreadsheet.
  dataRange.setValues(bookValues);
}
  1. Senaryo projenizi kaydedin.

Kod incelemesi

Bu kod üç bölüme ayrılmıştır:

1. Mevcut kitap bilgilerini okuyun

İşlevin ilk üç satırı, kodu daha okunabilir hale getirmeye yardımcı olmak için sabitleri tanımlar. Sonraki iki satırda, bookValues değişkeni, sayfanın kitap bilgilerinin yerel bir kopyasını tutmak için kullanılır. Kod, bookValues içindeki bilgileri okur, eksik bilgileri doldurmak için API'yi kullanır ve bu değerleri tekrar e-tabloya yazar.

2: Yardımcı işlevi kullanarak eksik bilgileri getirin

Kod, eksik başlıkları veya yazarları bulmak için bookValues içindeki her satırda döngü oluşturur. Verimliliği artırırken API çağrılarının sayısını azaltmak için kod yalnızca aşağıdakiler doğruysa API'yi çağırır:

  1. Satırın ISBN sütununda bir değer var.
  2. Satırdaki başlık veya yazar hücresi boş.

Koşullar doğruysa kod, daha önce uyguladığınız fetchBookData_(isbn) yardımcı işlevini kullanarak API'yi çağırır ve sonucu bookData değişkeninde saklar. Şimdi, sayfaya eklemek istediğiniz eksik bilgiler bu e-tabloda yer almalıdır.

Geriye yalnızca bookData bilgilerini e-tablomuza eklemek kaldı. Ancak bir uyarıda bulunalım. Ne yazık ki Open Library Book API gibi herkese açık API'ler bazen istediğiniz bilgileri içermeyebilir veya bilgilerin sağlanmasını engelleyen başka sorunlar olabilir. Her API isteğinin başarılı olacağını varsayarsanız kodunuz beklenmedik hataları işleyecek kadar sağlam olmaz.

Kodunuzun API hatalarını işleyebildiğinden emin olmak için, kodu kullanmayı denemeden önce API yanıtının geçerli olup olmadığını kontrol etmesi gerekir. Kod bookData olduktan sonra, bookData ve bookData.details'nin varlığını doğrulamak için basit bir kontrol gerçekleştirir ve bunlardan okumaya çalışmadan önce bu kontrolü yapar. Bu değerlerden biri eksikse API'nin istediğiniz verilere sahip olmadığı anlamına gelir. Bu durumda, continue komutu koda bu satırı atlamasını söyler. Eksik hücreleri dolduramazsınız ancak en azından komut dosyanız kilitlenmez.

3: Güncellenen bilgileri tekrar sayfaya yazın

Kodun son bölümünde, API'nin döndürdüğü başlık ve yazar bilgilerini doğrulamak için benzer kontroller yapılır. Kod, yalnızca orijinal başlık veya yazar hücresi boşsa ve API, oraya yerleştirebileceğiniz bir değer döndürdüyse bookValues dizisini günceller.

Döngü, sayfadaki tüm satırlar incelendikten sonra çıkar. Son adım, artık güncellenmiş olan bookValues dizisini Range.setValues(values) kullanarak e-tabloya geri yazmaktır.

Sonuçlar

Artık kitap verilerinizi temizleme işlemini tamamlayabilirsiniz. Aşağıdakileri yapın:

  1. Henüz yapmadıysanız e-tablonuzda A2:A15 aralığını vurgulayın ve virgül sorunlarını düzeltmek için Kitap listesi > Başlığı/yazarı ilk virgülden ayır'ı seçin.
  2. Henüz yapmadıysanız e-tablonuzda A2:A15 aralığını vurgulayın ve "by " sorunlarını temizlemek için Kitap listesi > Son"by " ifadesinde başlığı/yazarı ayır'ı seçin.
  3. Kalan tüm hücreleri doldurmak için Kitap listesi > Boş başlık ve yazar hücrelerini doldur'u seçin:

826675a3437adbdb.gif

13. Sonuç

Bu codelab'i tamamladığınız için tebrikler. Apps Komut Dosyası kodunuzun farklı bölümlerini etkinleştirmek için özel menüler oluşturmayı öğrendiniz. Ayrıca, Apps Komut Dosyası hizmetlerini ve herkese açık API'leri kullanarak verileri Google E-Tablolar'a nasıl aktaracağınızı da öğrendiniz. Bu, e-tablo işlemede yaygın bir işlemdir ve Apps Komut Dosyası, çeşitli kaynaklardan veri içe aktarmanıza olanak tanır. Son olarak, e-tablo verilerini okumak, işlemek ve eklemek için Apps Komut Dosyası hizmetlerini ve JavaScript'i nasıl kullanabileceğinizi öğrendiniz.

Bu codelab'i faydalı buldunuz mu?

Evet Hayır

Öğrendikleriniz

  • Google E-Tablosu'ndan veri içe aktarma
  • onOpen() işlevinde özel menü oluşturma
  • Dize veri değerlerini ayrıştırma ve işleme
  • URL Fetch Service'i kullanarak herkese açık API'leri çağırma
  • Herkese açık bir API kaynağından alınan JSON nesne verileri nasıl ayrıştırılır?

Sırada ne var?

Bu oynatma listesindeki bir sonraki codelab'de, e-tablodaki verileri biçimlendirme hakkında daha ayrıntılı bilgi verilmektedir.

Bir sonraki codelab'i Veri biçimlendirme sayfasında bulabilirsiniz.