LANG SELRCT

Apps Script Reference  (Create: Create new Spreadsheet | Create new Apps Script

Wednesday, July 18, 2018

Fusion Tableにデータを追加する(importRows)


大量のデータを追加する場合はimportRowsを使うと良いそうなので試しました

INSERT INTOを使う方法は以下の記事に書きました

INSERT INTOではすべての列にデータを入れなくても追加できましたが
importRowsでは存在するすべての列にデータを入れないと
以下のようなエラーが出て追加できないようです

Content has a different number of columns than the table



事前準備としてAPIを利用できるようにしておきます


Fusion TableにimportRowsでデータを追加するコードの例

その1


こういう形で追加するデータを用意する
  var records =
    "TWO, 2, ここ, 2018/07/18\n" +
    "THREE, 3, そこ, 2018/07/19\n";

コード.gs
function import_rows(){
  var records = 
    "TWO, 2, ここ, 2018/07/18\n" + 
    "THREE, 3, そこ, 2018/07/19\n";
  var blob = Utilities.newBlob(records, "application/octet-stream");
  FusionTables.Table.importRows(table_id, blob); 
}


import_rows()を実行すると以下のように行が追加されます
黄色いハイライトはそんな場所ないよってことらしいです
Locationのデータの正確性はこの記事では無視します

Locationはこのあたりが参考になりそう



その2


こういう形で追加するデータを用意する
  var values = [
                       ["FOUR", 4, "あそこ", "2018/07/20"],
                       ["FIVE", 5, "どこ", "2018/07/21"]
                     ];

コード.gs
function import_rows2(){
  var values = [["FOUR", 4, "あそこ", "2018/07/20"], ["FIVE", 5, "どこ", "2018/07/21"]];
  var records = "";
  for(var i = 0; i < values.length; i++){
    records += values[i] + "\n"
  }
  var blob = Utilities.newBlob(records, "application/octet-stream");
  FusionTables.Table.importRows(table_id, blob); 
}


import_rows2()を実行すると以下のように行が追加されます


その3


このようにスプレッドシートに用意したデータを取得して追加する

コード.gs
function import_rows3(){
  var ss_url = "対象のスプレッドシートURL";
  var ss = SpreadsheetApp.openByUrl(ss_url);
  var sh = ss.getSheets()[0];
  var range = sh.getDataRange();
  var values = range.getDisplayValues();
  var records = "";
  for(var i = 0; i < values.length; i++){
    records += values[i] + "\n"
  }
  var rowsBlob = Utilities.newBlob(records, "application/octet-stream");
  FusionTables.Table.importRows(table_id, rowsBlob); 
}


import_rows3()を実行すると以下のように行が追加されます



補足


シートのセル内に改行、シングルクオート、ダブルクオート、カンマなどがあるとそこで区切られてカラムの数が変わってしまうため消しておきます
(またはreplaceで全角などに変えます)

正規表現で消す例

.replace(/\r\n/g, "").replace(/\n/g, "").replace(/"/g, "").replace(/'/g, "") .replace(/,/g, "")

または
.replace(/\r\n|\n|"|'|,/g, "")

または全角に変える
.replace(/\r\n/g, "\r\n").replace(/\n/g, "\n").replace(/"/g, "”").replace(/'/g, "’") .replace(/,/g, ",").replace(/./g, ".");


スプレッドシートのURLを渡して
対象のシートを取得して
table_idのFusion TableにデータをINSERTする例


コード.gs
var table_id = "TABLE_ID";

function get_sheet_by_url() {
  var ss_url = "https://docs.google.com/spreadsheets/d/スプレッドシートIDedit#gid=シートID";
  var sh = get_sheet(ss_url);
  import_rows4(sh);
}

function get_sheet(ss_url) {
  var ss = SpreadsheetApp.openByUrl(ss_url);
  var sh_id = ss_url.split("gid=")[1];
  var shs = ss.getSheets();
  for (var i = 0; i < shs.length; i++) {
    if (shs[i].getSheetId() == sh_id) {
      break;
    }
  }
  var sheet = ss.getSheets()[i];
  return sheet;
}

function import_rows4(sh){
  var range = sh.getDataRange();
  var values = range.getDisplayValues();
  var records = "";
  for(var i = 0; i < values.length; i++){
    var array = values_without_newline(values[i]);
    records += array + "\n"
  }
  var rowsBlob = Utilities.newBlob(records, "application/octet-stream");
  FusionTables.Table.importRows(table_id, rowsBlob); 
}

function values_without_newline(values){
  var array = [];
  for(var i = 0; i < values.length; i++){
    array.push(values[i].replace(/\r\n|\n|"|'|,/g, ""));
  }
  return array;
}


Latest post

Extracting data from Google Sheets with regular expressions

Introduction Regular expressions are a powerful tool that can be used to extract data from text.  In Google Sheets, regular expressions ca...