【GAS】リンク付き(目次的な)シート一覧を作成する方法(Gスプレッドシート)

シートの操作

GAS シートの操作一覧

ご訪問ありがとうございます。

Googleシートマスターのひろしです。

前回

【GAS】現在のシートを最初/最後に移動させる方法(Gスプレッドシート)
  • シートを最初や最後に移動させることができるようになります。
  • 手軽に関数を呼び出せるようになります。
  • 今回は、とっても価値の高い方法をお伝えします。


    これを知ることであなたは、

    • HYPERLINK関数の使用方法がわかります。
    • GAS上で関数の引数を設定する方法がわかります。
    • シート一覧から各シートに飛べるようにできます。

    特に、関数の引数を組み立てる方法が
    わかると色々と応用できるので
    ぜひ、マスターしてください

    そして

     

    動画はこちら

    【GAS】Googleスプレッドシートリンク付きシート一覧の作成

    スクリプト

    //リンク付きシート一覧の作成
    function makehyperlink() {
      const ss = SpreadsheetApp.getActiveSpreadsheet()
      const url = ss.getUrl()
      const sheets = ss.getSheets() //すべてのシートを取得
    
    
      const hlinks = sheets.map(sheet => {
        const sheeturl = `${url}#gid=${sheet.getSheetId()}`
        const hlink = `=HYPERLINK("${sheeturl}","${sheet.getName()}")`
        return ([hlink])
      })
      console.log(hlinks)
      ss.getSheetByName("管理シート").getRange(2, 6, hlinks.length).setValues(hlinks)
    }
    ポイント
    テンプレートリテラル

    「テンプレートリテラル」を使用することで
    比較的簡単に、HYPERLINK関数の引数が作成できます。

    const surl = `${url}#gid=${sheet.getSheetId()}`

    const hlink = `=HYPERLINK(“${surl}”,”${sheet.getSheetName()}”)`

    の部分ですね。

     

    さらにこれを、

    【GAS】シート一覧の取得と表示/非表示の設定(Gスプレッドシート)
  • シート一覧を取得できるようになります。
  • シートの表示/非表示を切り替えられるようになります。
  • JavaScriptにおけるforEachやmapさらにfilterの例がわかります。
  • で作成したシート一覧を表示している
    関数に組み込みます。

    追加、変更箇所は ★

    //シートの一覧を取得
    function getListOfSheets() {
      const ss = SpreadsheetApp.getActiveSpreadsheet()
      const sheets = ss.getSheets() //すべてのシートを取得
    
      const url = ss.getUrl()   //★
    
    
      const sheetnames = sheets.map(sheet => {
    
    //ここから★
        const sheeturl = `${url}#gid=${sheet.getSheetId()}`
        const hlink = `=HYPERLINK("${sheeturl}","${sheet.getName()}")`
    //ここまで★
    
    
    
    
        return [hlink, sheet.isSheetHidden()]//★
      })
    
      // console.log(sheetnames)
    
      const mgws = ss.getSheetByName("管理シート")
    
      // console.log(sheetnames.length,sheetnames[0].length)
      mgws.getRange(2, 1, sheetnames.length, sheetnames[0].length).setValues(sheetnames)
    
    
    }

    これで、以前作成したシート一覧から
    シートに遷移できるようになりました。

    実際に作成したシートはこちら
    (閲覧権限のみなので動作しません)

    マインドセットします。

    マインドセット
    テンプレートリテラルを使用することで引数の指定が簡単になる

     

    では、今回のGoogleシートマスターへの究極の方法です。

    Step1.
    スプレッドシートに
    =HYPERLINK(
    と入力する

    Step2.
    適当にリンクと表示する名称を入力する

    Step3.

    HYPERLINKが機能することを確認する

    Step4.

    =HYPERLINK(。。。。。)
    をコピーしスクリプトエディタを開き貼り付ける

    Step5.

    テンプレートリテラルを使ってHYPERLINKの引数を作成する

    では、早速スプレッドシートに「HYPERLINK」と入力してください
    今すぐです。

    最後までご覧いただきありがとうございます。

    つぎはこちら

    【GAS】リンク付きシート一覧で非表示のシートを無視する方法
  • GASでシートが非表示かどうかを判別する方法がわかります。
  • 2次元バッファに格納する方法がわかります。
  • GAS シートの操作一覧

    コメント

    1. 湯川恵美 より:

      はじめまして!湯川と申します。
      いつも便利なGASの情報を参考にさせていただいております。

      【GAS】Googleスプレッドシートリンク付きシート一覧の作成

      上記を実際に使っていまして、常に更新されるシートの為
      スプレッドシート変更時にトリガーを設定しました。

      取得したリンク一覧の最終行で
      リンク追加は問題なく行われているのですが
      シートを削除しても消えずにそのまま残ってしまいます。

      解決策がございましたら、ご教示頂ければ幸いです。
      何卒宜しくお願い致します。

      • hiroshi より:

        湯川さん、コメントありがとうございます。

        全取得(一覧の再作成)を時折やるしかないと思います。

    タイトルとURLをコピーしました