Google Apps Scriptでリンク付きセルからURLだけを取り出す
ウェブで見かけた質問に対しての解答として作ってみました。Googleスプレッドシートでセルの内容に対してハイパーリンクが設定されてる場合、そのURLのみを取り出して別のセルに書き出すスクリプトになります。
ハイパーリンクはSpreadsheet上では「リッチテキスト」の１種とみなされる為、通常のgetValuesではなくgetRichTextValuesを使うのと、getLinkUrlを使うのがポイントです。
リンク
今回利用するサンプル
recordシートのA列にある「ハイパーリンクが設定されてるデータ」を一括で取得し、その中のURLだけを取り出してtargetシートのA列に書き出すスクリプトになっています。
図：ハイパーリンクが設定されてるセル
ソースコード
単純にコピーするコードの場合
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
//範囲丸ごとコピー function rangeCopy(){ //スプレッドシートを取得する let ss = SpreadsheetApp.getActiveSpreadsheet(); //シートを取得 let record = ss.getSheetByName("record"); //コピー元 let target = ss.getSheetByName("target"); //コピー先 //最終行を取得 let endrow = record.getLastRow(); //コピペ実行 record.getRange("A2:A" + endrow).copyTo(target.getRange(2, 1, endrow, 1),SpreadsheetApp.CopyPasteType.PASTE_NORMAL,false) } |
上記は単純にrecordシートからtargetシートへとコピーしてるだけのコードです。copyToの場合書式もそのままに完全コピーします。第三引数のfalseは転置する場合はTrueにしますが、あまり使う機会はないかと。
但しこれではURLだけ取り出すといったことは出来ません。
URLを取り出して貼り付けるコード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
//リンクだけ抽出して貼り付け function rangePickUrl(){ //スプレッドシートを取得する let ss = SpreadsheetApp.getActiveSpreadsheet(); //シートを取得 let record = ss.getSheetByName("record"); //コピー元 let target = ss.getSheetByName("target"); //コピー先 //リッチテキストとして取得 let richman = record.getRange("A2:A").getRichTextValues(); //recordのURLだけを取得 let richarr = []; //リンクだけを取り出す for(let i = 0;i<richman.length;i++){ //レコードを一個取り出す let rec = richman[i]; //リンクだけ取り出す let tempurl = rec[0].getLinkUrl(); //配列に追加する richarr.push([tempurl]); } //コピー先に書き出す let lastColumn = richarr[0].length; //カラムの数を取得する let lastRow = richarr.length; //行の数を取得する target.getRange(2,1,lastRow,lastColumn).setValues(richarr); } |
URLだけ取り出す場合には、getValuesではなくgetRichTextValuesを利用してリッチテキストを取り出します。単体のセルの場合はgetRichTextValueとなるので注意。
取り出した塊はオブジェクトが詰まった配列で返ってくるので、ループで一個ずつ取り出して、getLinkUrlにてハイパーリンクを取り出すことが可能です。rec[0]を指定していますが、二次元配列の常に0番目にしかオブジェクトは入っていません。
取得したものを別の２次元配列として組み立てて、最後に一気に書き出すようにします。
図：無事にリンクだけを取り出せました。
