Skip to content

Instantly share code, notes, and snippets.

@kariyayo
Last active June 21, 2016 12:59
Show Gist options
  • Save kariyayo/e8978cf907515ecb41ed00d239d0cb59 to your computer and use it in GitHub Desktop.
Save kariyayo/e8978cf907515ecb41ed00d239d0cb59 to your computer and use it in GitHub Desktop.
GoogleスプレッドシートからINSERT文を生成するGoogle Apps Script ref: http://qiita.com/bati11/items/4af429ba29493adeb22c
function create_insert() {
var headerRowIndex = 1;
var firstValuesRowIndex = headerRowIndex + 1;
var range = SpreadsheetApp.getActiveRange();
var cells = range.getValues();
var numRows = range.getNumRows();
var numColumns = range.getNumColumns();
var tableName = cells[0][0];
var columnNames = [];
for (var i = 0; i < numColumns; i++) {
columnNames.push(cells[headerRowIndex][i]);
}
var prefix = "INSERT INTO " + tableName + " (" + columnNames.join(",") + ") VALUES (";
var newSheet = range.getSheet().getParent().insertSheet();
var targetCell = newSheet.getActiveCell();
for (var i = firstValuesRowIndex; i < numRows; i++) {
var values = [];
for (var j = 0; j < numColumns; j++) {
values.push(cells[i][j]);
}
targetCell.setValue(prefix + values.join(",") + "); ");
targetCell = targetCell.offset(1, 0);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment