【GAS】特定列の文字を指定文字に一括変換したいときの小ネタ【スプレッドシート】

おはやうございます、管理人のPaZooです。
Advent Calendar4日目の記事にしては小ネタのような気もしますが、まあよかろう。。

さて、今回仕入れたネタは「GAS」です。
タイトルにもある通り、「GASで特定列の文字を指定文字に一括変換したい！」という時に使えます！

管理人は、現場でスプレッドシートからCSVへ変換したいときに文字列にエスケープ文字「\r\n」・「\n」・「\r」が含まれている場合に空白文字に削除したかったので今回の実装を行いました。

スプレッドシート

GASソースコード

function myFunction() {

  //元データ取得

  var SSCopyForm = SpreadsheetApp.openById("***SpreadsheetId***");

  var SSCopyFromSheetsName = SSCopyForm.getSheetByName("シート名をここに記載");

  //元データの最終行

  var LastRow = SSCopyFromSheetsName.getLastRow();　//最終行を取得

  var LastColumn = SSCopyFromSheetsName.getLastColumn();　//最終列を取得

  //元データの取得した最終列、最終行までに入力された値を取得する

  var CopyValue = SSCopyFromSheetsName.getRange(1,1,LastRow,10).getValues();

  //貼り付け先のスプレッドシートのIDを指定してシート名を指定する

　var SSCopyTo = SpreadsheetApp.openById("***SpreadsheetId***");

  var SSCopyToSheetsName = SSCopyTo.getSheetByName("シート名をここに記載");

 //シートクリア

SSCopyToSheetsName.clear();

//コピーした値を貼り付ける

SSCopyToSheetsName.getRange(1,1,LastRow,10).setValues(CopyValue);

//貼り付けた値から削除対象文字を検索

SSCopyToSheetsName.getRange("I2:I"+SSCopyToSheetsName.getLastRow()) //I列を選択

  .createTextFinder('\r\n|\r|\n') //対象の改行コードを指定

  .useRegularExpression(true)　//正規表現を使用

  .replaceAllWith("");

}

 

解説

①コピー元シートの情報を取得

  //元データ取得

  var SSCopyForm = SpreadsheetApp.openById("***SpreadsheetId***");

  var SSCopyFromSheetsName = SSCopyForm.getSheetByName("シート名をここに記載");

  //元データの最終行

  var LastRow = SSCopyFromSheetsName.getLastRow();　//最終行を取得

  var LastColumn = SSCopyFromSheetsName.getLastColumn();　//最終列を取得

  //元データの取得した最終列、最終行までに入力された値を取得する

  var CopyValue = SSCopyFromSheetsName.getRange(1,1,LastRow,10).getValues();

今回の実装では、「コピー元シートをコピー先シートに複製してから、改行コードを消してほしい」という要望が挙がっていたので上のコードではコピー元データを取得しています。

②コピー先シートの情報を取得し、コピー元シートの情報を貼り付ける

  //貼り付け先のスプレッドシートのIDを指定してシート名を指定する

  var SSCopyTo = SpreadsheetApp.openById("***SpreadsheetId***");

  var SSCopyToSheetsName = SSCopyTo.getSheetByName("シート名をここに記載");

 //シートクリア

SSCopyToSheetsName.clear();

//コピーした値を貼り付ける

SSCopyToSheetsName.getRange(1,1,LastRow,10).setValues(CopyValue);

 

コピー先シートのシートクリアを行い、コピー元シート情報の値をコピー先シートに貼り付けます。

③貼り付けされたコピー先シートから削除対象文字を検索して空白文字に一括置換

 

//貼り付けた値から削除対象文字を検索

SSCopyToSheetsName.getRange("I2:I"+SSCopyToSheetsName.getLastRow()) //I列を選択

  .createTextFinder('\r\n|\r|\n') //対象の改行コードを指定

  .useRegularExpression(true)　//正規表現を使用

  .replaceAllWith("");

}

ここで、貼り付けされたコピー先シートから削除対象文字を検索するため
「createTextFinder() 」メソッドで削除対象文字の「'\r\n|\r|\n'」を指定。

そのあとに、正規表現を使うようにtrueで定義して当該文字列に対して置換(今回は削除する)を行うよう指定してます。

 

短いですが、今回はここまで！

お役に立てれば幸いです！

 

追記

はてな記法が使えなくてソースコードを引用で表現してます、すみません！

近いうち修正いれます！