今回はURL短縮サービスと連携してURLリストを一括で短縮化する仕組みをGoogleAppsScriptを使って構築していきます。
この状態から実行することで

C列に一括変換されたURLが表示できるところまでがゴールです。

今回はサンプルとしてYahooショッピングの検索後のURLを利用しました。
URL短縮サービスを登録する
今回URL短縮サービスとして一番メジャーであるBitlyを利用します。URL短縮サービスはGoogleでも提供していましたが、2019/3に提供を終了しました。
アカウントを登録する
こちらのURLよりアカウント登録を行います。
https://bitly.com/a/sign_up
Google連携も出来ますが、その後のアクセストークン発行時のパスワード変更が必要になるため、専用のアカウントを発行しましょう。
※GoogleやTwitter連携で作成した場合、後述手順のアクセストークン発行前にパスワード変更が必要です(current passwordは空でリセットする)
赤枠線内の情報を入力してbitlyのアカウントを作成します。

アクセストークンを発行する
アカウント作成からログインを行うと画面右上にメニューボタンがあるためクリックします。

続けてアカウント名が表示されている領域をクリックします。

「Ceneric Access Token」をクリックします。

パスワード入力欄が出るので、アカウントのパスワードを入力しメニュー下部にある「GENERATE TOKEN」をクリックします。

成功すると以下の通り、アクセストークンが表示されますので、Copyボタンを押して、文字列を控えておきます。

GoogleAppScriptからbitlyAPIを実行する
まずはB列に変換前のURLを並べたシートを準備します。

続けてスクリプトエディタで以下コードを記述します。
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// sampleシートのオブジェクトを取得
var sampleSheet = ss.getSheetByName('sample');
var checkCnt = 2;
while(true) {
// シートのB2が処理を開始、B3,B4..と続ける
var val = sampleSheet.getRange(checkCnt, 2).getValue();
// BXXが空の場合は処理を終了する
if (val == '') {
break;
}
// 空でない場合は取り出した値を同じ行のC列にset
sampleSheet.getRange(checkCnt, 3).setValue(val);
// 次の行を処理対象とするためにcheckCntをインクリメント(+1)する
checkCnt++;
}
}
この状態で実行するとB列の文字列がC列にコピーされます。あとは単純にコピーするだけではなく変換後の値にしてあげることで処理完了です。
続けて変換処理用のfunctionを作成してみます。functionの仕様は変換元URLを引数にコールすると結果として短縮後のURLが返ってくる仕様にします。
function bitlyService(val) {
// ここでbitlyから取得したアクセストークン文字列を入れる
var token = 'XXX';
// bitlyAPIのURLを生成する。元URLはエンコード処理をするためにencodeURIComponentを使う
var bitlyUrl = 'https://api-ssl.bitly.com/v3/shorten?access_token=' + token + '&longUrl=' + encodeURIComponent(val);
// API実行
var options = {
"method" : "get",
"contentType" : "application/json"
};
var result = UrlFetchApp.fetch(bitlyUrl, options);
var json = JSON.parse(result.getContentText('utf-8'));
// 短縮後のURLを返却
return json.data.url;
}
最後にシートの値を使って変換処理を呼ぶ処理に差し替えて完了です。
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// sampleシートのオブジェクトを取得
var sampleSheet = ss.getSheetByName('sample');
var checkCnt = 2;
while(true) {
// シートのB2が処理を開始、B3,B4..と続ける
var val = sampleSheet.getRange(checkCnt, 2).getValue();
// BXXが空の場合は処理を終了する
if (val == '') {
break;
}
// 空でない場合は取り出した値を同じ行のC列にset
// ★変更点:bitlyService(val)をセルに書き込むように修正
sampleSheet.getRange(checkCnt, 3).setValue(bitlyService(val));
// 次の行を処理対象とするためにcheckCntをインクリメント(+1)する
checkCnt++;
}
}
function bitlyService(val) {
// ここでbitlyから取得したアクセストークン文字列を入れる
var token = 'XXX';
// bitlyAPIのURLを生成する。元URLはエンコード処理をするためにencodeURIComponentを使う
var bitlyUrl = 'https://api-ssl.bitly.com/v3/shorten?access_token=' + token + '&longUrl=' + encodeURIComponent(val);
// API実行
var options = {
"method" : "get",
"contentType" : "application/json"
};
var result = UrlFetchApp.fetch(bitlyUrl, options);
var json = JSON.parse(result.getContentText('utf-8'));
// 短縮後のURLを返却
return json.data.url;
}
実行して成功すれば以下の通り、C列に短縮後のURLが書き込まれます。

まとめ
今回はWebAPIを使って結果をシート内に書き込む処理を作成しました。このパターンが自分で作れるようになると様々なAPIとの連携が出来るようになるので、本サンプルをベースとして色々と拡張してみてください。