【GAS】Google Apps Script 活用事例 商品イメージのURLを入力したら、IMAGE関数を自動入力してくれるスクリプト
コロナ禍での内定者懇親会という事で、オンライン飲み会時の軽食を調査するお仕事をしています。商品イメージが伝わるようにIMAGE関数を各行に挿入しているのですが、URLが各サイトごとに違うので、ドラッグしたりとか計算式のみコピペができず結構面倒くさいんですよね。そこでURLを入力したら、IMAGE関数を自動挿入してくれる関数を作りました。
IMAGE関数とは
スクリプト全文はこちら
/**
*
* 調査のシートで1列目だったら、IMAGE関数を挿入
* それ以外だったら、自動でテキスト折り返しと垂直中央に合わせる
*
*/
function searchLightMeal(){
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('軽食調査');
const activeSheet = spreadsheet.getActiveSheet();
//シート名が違ったら処理を終了
if(sheet.getName() !== activeSheet.getName()){return}
const activeCell = getActiveCell_(sheet);
const headerRowRange = sheet.getRange(10, 1, 1, sheet.getLastColumn());
const headerRow = headerRowRange.getValues().flat();
console.log(`範囲： ${headerRowRange.getA1Notation()}`);
console.log(headerRow);
const columns = {
image: headerRow.indexOf('商品イメージ') + 1,
url: headerRow.indexOf('サイトURL') + 1,
}
console.log(columns);
//イメージ関数を自動挿入する
if(activeCell.column === columns.image){
const formula = `=IMAGE("${activeCell.value}")`;
console.log(`挿入された数式： ${formula}`);
sheet.getRange(activeCell.range)
.setFormula(formula)
.setVerticalAlignment('middle');
}else{
//それ以外の列であれば、テキストの折り返しと垂直方向を中央に揃える
sheet.getRange(activeCell.range)
.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP)
.setVerticalAlignment('middle');
console.log('テキストを折りたたみました。');
}
}
function getActiveCell_(sheet) {
const activeCell = sheet.getActiveCell();
const cellDetail = {
row: activeCell.getRow(),
column: activeCell.getColumn(),
value: activeCell.getValue(),
range: activeCell.getA1Notation()
}
console.log(cellDetail);
return cellDetail
}URLを入力したらサイト名を書き出すスクリプトも書きました。
function setUrlTitle() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('競合調査');
const activeSheet = spreadsheet.getActiveSheet();
//シート名の取得
const targetSheetName = sheet.getSheetName();
const activeSheetName = activeSheet.getSheetName();
if(targetSheetName !== activeSheetName){
return
}
console.log('指定したシート：%s', targetSheetName);
console.log('アクティブなシート：%s', activeSheetName);
//アクティブセルを取得
const activeCell = sheet.getActiveCell();
const activeDetail = {
row: activeCell.getRow(),
column: activeCell.getColumn(),
value: activeCell.getValue(),
range: activeCell.getA1Notation()
}
console.log(activeDetail);
if(activeDetail.value.includes('https') === true){
const targetColumn = activeDetail.column - 1;
sheet.getRange(activeDetail.row, targetColumn).setFormula(`=IMPORTXML(${activeDetail.range},"html/head/title")`);
}else{
return
}
}