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

1. Giriş

Google E-Tablolar ile Codelab oynatma listesinin üçüncü bölümüne hoş geldiniz.

Bu codelab'i tamamlayarak E-Tablolar deneyiminizi iyileştirmek için Apps Komut Dosyası'ndaki veri manipülasyonunu, özel menüleri ve herkese açık API veri erişimini nasıl kullanacağınızı öğrenebilirsiniz. Bu oynatma listesindeki önceki codelab'lerde kullanıma sunulan SpreadsheetApp, Spreadsheet, Sheet ve Range sınıflarıyla çalışmaya devam edeceksiniz.

Neler öğreneceksiniz?

  • Kişisel veya paylaşılan bir e-tablodaki Drive'da veri 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 alma ve işleme.

Başlamadan önce

Bu, 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, E-Tablolar ve Aralıklar

Gerekenler

  • Bu oynatma listesinin önceki codelab'lerinde incelenen temel Apps Komut Dosyası konuları hakkında bilgi edinin.
  • Apps Komut Dosyası düzenleyicisine dair temel bilgi sahibi olma
  • Google E-Tablolar'la ilgili temel bilgi sahibi olma
  • E-Tablolar A1 Gösterimi'ni okuma özelliği
  • JavaScript ve String sınıfı hakkında temel bilgi sahibi olma

2. Kur

Bu codelab'deki alıştırmaların çalışması için bir e-tablo gerekiyor. Bu alıştırmalarda kullanmak üzere 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ünden Yeni &Google E-Tablolar'ı seçerek yapabilirsiniz. Bu işlem, yeni e-tablonuzu oluşturur ve açar. Dosya, Drive klasörünüze kaydedilir.
  2. E-tablo başlığını tıklayıp "Başlıksız e-tablo" ve "Veri Manipülasyonu ve Özel Menüler" olarak değiştirin. E-tablonuz 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 bunu "Adsız Proje"den "Veri Manipülasyonu ve Özel Menüler"e çevirin. Başlık değişikliğini kaydetmek için Yeniden adlandır'ı tıklayın.

Boş bir e-tablo ve projeyle laboratuvarı başlatmaya hazırsınızdır. Özel menüler hakkında bilgi edinmeye başlamak için bir sonraki bölüme geçin.

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

Apps Komut Dosyası, Google E-Tablolar'da görüntülenebilecek özel menüler tanımlamanıza olanak tanır. Google Dokümanlar, Google Slaytlar ve Google Formlar'da özel menüler de kullanabilirsiniz. Özel menü öğesi tanımlarken bir metin etiketi oluşturur ve bu komut dosyasını projenizdeki Apps Komut Dosyası işlevine bağlarsınız. Ardından, menüyü Google E-Tablolar'da görünmesi için kullanıcı arayüzüne ekleyebilirsiniz:

d6b694da6b8c6783.png

Bir kullanıcı özel bir menü öğesini tıkladığında, onunla ilişkilendirdiğiniz Apps Komut Dosyası işlevi yürütülür. Bu, komut dosyası düzenleyiciyi açmak zorunda kalmadan Apps Komut Dosyası işlevlerini çalıştırmanın hızlı bir yoludur. Ayrıca, e-tablonun diğer kullanıcılarının, nasıl veya Apps Komut Dosyası'nın nasıl çalıştığına dair bilgi sahibi olmak zorunda kalmadan kodunuzu yürütmesine olanak tanır. Onlar için sadece bir diğer menü öğesi.

Ö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ı kodu çalıştırmak için bir yöntem sunar. Tetikleyici oluşturduğunuzda, tetikleyicinin etkinleşmesine neden olan etkinliği tanımlar ve etkinlik için çalışan bir Apps Komut Dosyası işlevi sağlarsınız.

onOpen() basit bir tetikleyici örneğidir. Kurulumu kolaydır. onOpen() adlı bir Apps Komut Dosyası işlevi yazmanız yeterlidir. Apps Komut Dosyası, ilişkili e-tablo her açıldığında veya yeniden yüklendiğinde bunu ç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ğıdaki gibi 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. Komut dosyası projenizi kaydedin.

Kod incelemesi

Nasıl çalıştığını anlamak için bu kodu inceleyelim. onOpen() öğesinde ilk satır, bu komut dosyasının bağlı olduğu etkin e-tablonun kullanıcı arayüzünü temsil eden Ui nesnesini almak için getUi() yöntemini kullanır.

Sonraki üç satır, menüyü (Book-list) oluşturur, bu menüye bir menü öğesi (Load Book-list) ekler ve ardından menüyü e-tablo arayüzüne ekler. 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 seçildiğinde menü öğesi etiketi ile çalışan Apps Komut Dosyası işlevi arasında bir bağlantı oluşturur. Bu durumda, Load Book-list menü öğesinin seçilmesi E-Tablolar'ın loadBookList() işlevini çalıştırmasına (henüz mevcut değildir) neden olur.

Sonuçlar

Çalıştığından emin olmak 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üzenleyicinizi içeren sekmeyi kapatır.
  2. Araçlar > Komut dosyası düzenleyicisi'ni seçerek komut dosyası düzenleyicinizi yeniden açın.

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

687dfb214f2930ba.png

Kitap listesi'ni tıklayarak sonuç menüsünü görebilirsiniz:

8a4a391fbabcb16a.png

Bir sonraki bölümde loadBookList() işlevi için kod oluşturulacak ve Apps Komut Dosyası'ndaki verilerle etkileşimde bulunmanın bir yolu sunulacaktır: Diğer e-tabloları okuma.

5. E-tablo verilerini içe aktarma

Artık özel menü oluşturduğunuza göre, menü öğesini tıklayarak çalıştırılabilen işlevler oluşturabilirsiniz.

Şu anda, Book-list özel menüsünün bir menü öğesi var: Load Book-list. Load Book-list menü öğesini seçtiğinizde loadBookList(), işlevi, komut dosyanızda mevcut olmadığından bu işlev Kitap listesi > Kitap Listesi Yükle'yi seçtiğinizde hata verir:

b94dcef066e7041d.gif

Bu hatayı düzeltmek için loadBookList() işlevini uygulayabilirsiniz.

Uygulama

Yeni menü öğesinin, e-tabloyu üzerinde çalışacağı verilerle doldurmasını istersiniz. Dolayısıyla, loadBookList() başka e-tablodaki kitap verilerini okuyup bu e-tabloya kopyalamak için uygulanır:

  1. onOpen() altındaki 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. Komut dosyası projenizi kaydedin.

Kod incelemesi

Peki bu işlev nasıl çalışıyor? loadBookList() işlevi, önceki codelab'lerde tanıtılan birincil olarak Spreadsheet, Sheet ve Range sınıflarından 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 sayfayı tanımlayın

İlk satır, mevcut sayfa nesnesine referans almak için SpreadsheetApp.getActiveSheet() kullanır ve bunu sheet değişkeninde depolar. Bu, verilerin kopyalanacağı sayfadır.

2: Kaynak verileri belirleyin

Sonraki birkaç satır, geri aldığınız kaynak verilere işaret eden dört değişken oluşturur:

  • bookSS, kodun verilerini okuduğu e-tabloya yönelik bir referans depolar. Kod, e-tabloyu e-tablo kimliğine göre bulur. Bu örnekte, okunacak bir kaynak e-tablonun kimliğini sağladık ve e-tabloyu SpreadsheetApp.openById(id) yöntemini kullanarak açtık.
  • bookSheet, bookSS içinde istediğiniz verileri içeren bir sayfaya ilişkin referansı depolar. Kod, okunacak sayfayı codelab-book-list adıyla tanımlar.
  • bookRange, bookSheet içindeki bir veri aralığına yönelik referansı depolar. Sheet.getDataRange() yöntemi, sayfadaki boş olmayan tüm hücreleri içeren aralığı döndürür. Boş satırlar ve sütunlar eklemeden bir sayfadaki tüm verileri kapsayan bir aralık elde etmenin kolay yoludur.
  • bookListValues, bookRange hücresindeki hücrelerden alınan tüm değerleri içeren 2D 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 etiketine kopyalar ve ardından sayfayı yeniden adlandırır:

4: Hedef sayfayı biçimlendirme

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) özelliğini kullanarak yeni verileri daha kolay bir şekilde okumanıza olanak tanır.

Sonuçlar

Bu işlevi çalışırken görebilirsiniz. Google E-Tablolar'da, e-tablonuzu dolduracak işlevi çalıştırmak için Kitap listesi > Kitap listesi yükle'yi seçin:

3c797e1e2b9fe641.gif

Artık kitap başlıkları, yazarlar ve 13 haneli ISBN numaralarının listesini içeren bir sayfanız var. Sonraki bölümde, dize manipülasyonu 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 kitaba başvuruda bulunur ve başlığını, yazarını ve ISBN numarasını ayrı sütunlarda listeler. Ancak, bu ham verilerle ilgili bazı sorunlar da görebilirsiniz:

  1. Bazı satırlarda başlık ve yazar başlık sütununa birlikte, virgül veya "&"; dizesiyle bağlantılı olarak yerleştirilir.
  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 "&" karakterini ayırıcı olarak bulan bir metin yazar ve ilgili yazar ve başlık alt dizelerini doğru sütunlara yerleştirirsiniz. İkinci sorun için eksik kitap bilgilerini harici bir API ile otomatik olarak arayan ve bu bilgileri e-tablonuza ekleyen bir kod yazarsınız.

7. Menü öğeleri ekleyin

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

Uygulama

İhtiyacınız olan ekstra menü öğelerini eklemek için onOpen() öğesini 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. Komut dosyası projenizi kaydedin.
  2. Komut dosyası düzenleyicisinde, işlevler listesinden onOpen'yi seçin ve Çalıştır'ı tıklayın. Bu işlem, e-tabloyu yeniden oluşturmak zorunda kalmamanız için e-tablo menüsünü yeniden oluşturmak üzere onOpen() öğesini çalıştırır.

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

Sonuçlar

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

580c806ce8fd4872.png

Bu yeni öğeleri tıklamak, karşılık gelen işlevlerini uygulamadığınız için bir hataya neden olur. Bu nedenle bu işlemi şimdi yapalım.

8. Virgülle ayrılmış ayırıcılardaki metni böl

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

ca91c43c4e51d6b5.png

Metin dizelerini ayrı sütunlara bölmek yaygın bir e-tablo görevidir. Google E-Tablolar, dizeleri sütunlara bölen bir SPLIT() işlevi sağlar. Ancak, veri kümelerinde genellikle E-Tablolar'ın yerleşik işlevleriyle kolayca çözülemeyen sorunlar vardır. Böyle durumlarda, verilerinizi temizlemek ve düzenlemek için gereken karmaşık işlemleri yapmak üzere Apps Komut Dosyası kodu yazabilirsiniz.

Virgül bulunduğunda, yazar ve başlığı ilgili hücrelere bölen 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 olan hücreleri temsil eden aralığı al.
  2. Aralıktaki hücrelerin virgül olup olmadığını kontrol edin.
  3. Virgüller bulunduğunda, dizeyi ilk virgülün konumunda iki (ve yalnızca iki) alt dizeye bölün. İşleri daha basit hale getirmek için herhangi bir virgülün "[authors], [title]" adlı dize kalıbını gösterdiğini varsayabilirsiniz. Hücrede birden fazla virgül varsa bunun dizedeki ilk virgül ile bölünmeye uygun olduğunu 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 kullanmanız gerekir, ancak dize verilerini değiştirmek için JavaScript de kullanmanız gerekir. Aşağıdaki adımları uygulayın:

  1. Apps Komut Dosyası düzenleyicisinde, 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. Komut dosyası projenizi kaydedin.

Kod incelemesi

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

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

İlk üç satır, sayfadaki mevcut verilere başvuruda 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 bunu yalnızca A sütunundaki hücreleri vurgulayarak yaptığını varsayabiliriz.
  • titleAuthorRange, activeRange ile aynı hücreleri kapsayan, ancak sağ tarafta 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, genişletilmiş yazarları başlık sütununa koyabileceği bir yere ihtiyacı olduğundan, bu genişletilmiş aralığın kullanılması gerekir.
  • titleAuthorValues, Range.getValues() kullanılarak titleAuthorRange kaynağından alınan 2D veri dizisidir.

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

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

  1. Hücre dizesi değeri titlesAndAuthors değişkenine kopyalanır.
  2. Virgülle belirlenen konum, JavaScript String indexOf() yöntemi kullanılarak belirlenir.
  3. JavaScript Dizesi dilim() yöntemi, ayırıcıdan önce alt dizeyi ve ayırıcıdan sonra alt dizeyi almak için iki kez çağrılır.
  4. Alt dizeler titleAuthorValues 2D dizisine kopyalanarak ilgili konumdaki mevcut değerlerin üzerine yazılır. "Yazarlar], [başlık]" kalıbını varsadığımız için iki alt dizenin sırası, başlığı ilk sütuna, yazarları ikinci sütuna yerleştirmek için tersine çevrilir.

Not: Kod virgül bulamadığında satırdaki veriler değişmez.

3: Yeni değerleri tekrar sayfaya kopyalayın

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

Sonuçlar

Artık splitAtFirstComma() işlevinin etkilerini çalışırken görebilirsiniz. Seçtikten sonra İlk virgül/başlık birbirinden ayır menü öğesini seçerek çalıştırmayı deneyin.

...bir hücre:

a24763b60b305376.gif

...veya birden fazla hücre içeriyorsa:

89c5c89b357d3713.gif

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

9. "Ayırıcı"da metin bölme

Orijinal verilere baktığınızda başka bir sorun görebilirsiniz. Bazı veri yapılarının başlıkları ve yazarları tek bir hücrede "(yazarlar], [başlık]" biçiminde olduğu gibi, diğer hücrelerde de yazar ve başlık türü [title] adlı yazara göre "[title]" olarak biçimlendirilir:

41f0dd5ac63b62f4.png

Uygulama

Bu sorunu, son bölümün tekniğini kullanarak splitAtLastBy() adlı bir işlev oluşturarak çözebilirsiniz. Bu işlev splitAtFirstComma() ile benzer bir işe sahiptir. Gerçek tek fark, biraz farklı bir metin kalıbı aramaktır. Bu işlevi aşağıdaki işlemleri gerçekleştirerek uygulayın:

  1. Apps Komut Dosyası düzenleyicisinde, 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. Komut dosyası projenizi kaydedin.

Kod incelemesi

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

  1. "by"alt dizesi, ","yerine dize ayırıcı olarak kullanılır.
  2. Burada String.indexOf(substring) yerine JavaScript String.lastIndexOf(substring) yöntemi kullanılır. Bu, ilk dizede, son"by"haricindeki tüm"by"alt dizeleri varsa başlığın bir parçası olduğu varsayılır.
  3. Dize bölündükten sonra ilk alt dize, başlık ve ikincisi yazar olarak ayarlanır (bu, splitAtFirstComma() ile tersidir).

Sonuçlar

Artık splitAtLastBy() işlevinin etkilerini çalışırken görebilirsiniz. Son olarak ayrı başlık/yazar menü öğesini seçerek çalıştırmayı deneyin...

...bir hücre:

4e6679e134145975.gif

...veya birden fazla hücre içeriyorsa:

3c879c572c61e62f.gif

Codelab'in bu bölümünü tamamladınız. Artık Apps Komut Dosyası'nı bir sayfadaki dize verilerini okumak ve değiştirmek için kullanabilir ve farklı Apps Komut Dosyaları komutlarını çalıştırmak için özel menüleri kullanabilirsiniz.

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

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

Şu ana kadar veri kümenizi bazı başlık ve yazar biçimlendirme sorunlarını düzeltmek için hassaslaştırdınız, ancak veri kümesinde aşağıdaki hücrelerde vurgulanan bazı bilgiler eksik:

af0dba8cb09d1a49.png

Mevcut verilerdeki dize işlemlerini kullanarak eksik verileri alamazsınız. Bunun yerine, eksik verileri başka bir kaynaktan almanız gerekir. Bunu, ek veriler sağlayabilecek harici API'lerden bilgi isteyerek Apps Komut Dosyası'nda yapabilirsiniz.

API'ler uygulama programlama arayüzleridir. Genel bir terimdir, ancak temel olarak programlarınızın ve komut dosyalarınızın bilgi istemek veya belirli işlemleri yapmak için arayabilecekleri bir hizmettir. Bu bölümde, e-tablonuzdaki boş hücrelere ekleyebileceğiniz kitap bilgileri istemek için herkese açık bir API'yi çağırıyorsunuz.

Bu bölümde şu konularda bilgi verilmektedir:

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

11. UrlFetch ile harici verileri getirme

Doğrudan e-tablonuzla çalışan kodu ayrıntılı olarak incelemeden önce, herkese açık Open Library API'den kitap bilgilerini istemek için özel olarak bir yardımcı işlev oluşturarak Apps Komut Dosyası'nda harici API'lerle çalışma hakkında bilgi edinebilirsiniz.

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

Uygulama

Aşağıdakileri yaparak bu yardımcı işlevi uygulayın:

  1. Apps Komut Dosyası düzenleyicisinde, komut dosyanızın sonuna aşağıdaki kodu 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. Komut dosyası projenizi kaydedin.

Kod incelemesi

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

1: API isteği

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

url değişkeni, bir web adresi gibi bir URL dizesidir. Bu dosya, Açık Kitaplık sunucularındaki bir konuma işaret eder. Ayrıca, Açık Kitaplık sunucularına hangi bilgileri istediğinizi ve yanıtı nasıl yapılandıracağınızı 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şturmanızın ardından 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öndererek elde edilen yanıtı response değişkeninde depolar. URL'ye ek olarak, kod isteğe bağlı muteHttpExceptions parametresini true olarak ayarlar. Bu ayar, istek bir API hatasıyla sonuçlanırsa kodunuzun durdurulacağı 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ı, bir yanıt kodu, HTTP üst bilgileri ve ana yanıt içeriği içerir. Buradaki önemli bilgiler, ana JSON içeriğidir. Bu nedenle, kodun bu bilgileri çıkarması ve ardından, istenen bilgileri bulup döndürmek için JSON'u ayrıştırması gerekir.

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

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 tam içeriği ve biçimi 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) kodunu uyguladığınıza göre kodunuzdaki diğer işlevler, ISBN numarasını kullanan kitaplarla ilgili bilgileri bulabilir. Bu işlevi, e-tablonuzdaki hücreleri doldurmaya yardımcı olması için kullanırsınız.

12. API verilerini e-tabloya yazma

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

  1. Etkin veri aralığında 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 alma.
  3. Eksik başlık veya yazar değerlerini ilgili hücrelerde güncelleyin.

Uygulama

Aşağıdakileri yaparak bu yeni işlevi uygulayın:

  1. Apps Komut Dosyası düzenleyicisinde, komut dosyası projenizin sonuna aşağıdaki kodu 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. Komut dosyası projenizi kaydedin.

Kod incelemesi

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

1: Mevcut kitap bilgilerini okuyun

İşlevin ilk üç satırı, kodun daha okunaklı olmasına yardımcı olmak için sabit değerleri 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 kaynağından gelen bilgileri okur, eksik bilgileri doldurmak için API'yi kullanır ve bu değerleri e-tabloya yazar.

2: Yardımcı işlevi kullanarak eksik bilgileri alın

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 geçerliyse API'yi çağırır:

  1. Satır ISBN sütununun bir değeri 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 depolar. E-tabloda, e-tabloya eklemek istediğiniz eksik bilgiler bulunmalıdır.

Kalan tek görev, bookData bilgilerini e-tablonuza eklemektir. Ancak bir uyarı da var. Maalesef bazen Açık Kitaplık Kitap API'sı gibi herkese açık API'ler istediğiniz bilgilere sahip olmayabilir veya nadiren bu bilgilerin sağlanmasına engel olan başka sorunlar olabilir. Her API isteğinin başarılı olacağını varsayarsanız kodunuz beklenmeyen hataları işleyebilecek kadar sağlam değildir.

Kodunuzun API hatalarını işleyebildiğinden emin olmak için kod, API yanıtını kullanmaya çalışmadan önce geçerli olduğunu kontrol etmelidir. Kod bookData kodunu aldıktan sonra, okumayı denemeden önce bookData ve bookData.details öğelerinin mevcut olduğunu doğrulamak için basit bir kontrol gerçekleştirir. Bunlardan birinin eksik olması, API'nin istediğiniz verilere sahip olmadığı anlamına gelir. Bu durumda continue komutu, kodu ilgili satırı atlamasını sağlar. Eksik hücreleri dolduramazsınız, ancak en azından komut dosyanız kilitlenmez.

3: Güncel bilgileri 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 bulunur. Kod, yalnızca orijinal başlık veya yazar hücresi boşsa ve API buraya 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, yeni güncellenmiş bookValues dizisini Range.setValues(values) kullanarak e-tabloya yazmaktır.

Sonuçlar

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

  1. Henüz yapmadıysanız e-tablonuzdaki A2:A15 aralığını vurgulayın ve virgül sorunlarını temizlemek için İlk listede "Kitap listesi ve yazarı ayır"ı seçin'i seçin.
  2. Henüz yapmadıysanız sayfanızdaki A2:A15 aralığını vurgulayın ve sorunları gidermek için Kitap listesini > Sonuncusu/yazarını ayır'ı seçin.
  3. Kalan tüm hücreleri doldurmak için, Kitap listesi > Boş başlıkları ve yazar hücrelerini doldur'u seçin:

826675a3437adbdb.gif

13. Sonuç

Tebrikler, bu codelab'i tamamladınız. Apps Komut Dosyası kodunuzun farklı bölümlerini etkinleştirmek için özel menülerin nasıl oluşturulacağını öğrendiniz. Ayrıca, Apps Komut Dosyası hizmetlerini ve herkese açık API'leri kullanarak Google E-Tablolar'a veri aktarmayı öğrendiniz. Bu, e-tablo işlemede yaygın olarak yapılan bir işlemdir. Apps Komut Dosyası, çok çeşitli kaynaklardaki verileri 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 gördünüz.

Bu codelab'i faydalı buldunuz mu?

Evet Hayır

Öğrendikleriniz

  • Google e-tablosundan veri içe aktarma.
  • onOpen() işlevinde özel menü oluşturma.
  • Dize veri değerlerini ayrıştırma ve değiştirme.
  • URL Getirme Hizmeti'ni kullanarak herkese açık API'leri çağırma.
  • Herkese açık bir API kaynağından alınan JSON nesne verilerini ayrıştırma.

Sonraki adımlar

Bu oynatma listesindeki bir sonraki codelab'de, e-tablodaki verilerin nasıl biçimlendirileceği daha ayrıntılı şekilde açıklanmaktadır.

Bir sonraki codelab'i Veri biçimlendirme'de bulabilirsiniz.