Skip to content

Instantly share code, notes, and snippets.

@daichan4649 daichan4649/main.gs
Last active Mar 25, 2016

Embed
What would you like to do?
spreadsheet -> google calendar へ一括登録
var URL_BOOK = '[spreadsheet URL]';
var SHEETNAME = '[sheet name]';
var CALENDAR_ID = '[calendar id]';
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('管理者用')
.addItem('カレンダーへ登録', 'register')
.addToUi();
}
function register() {
// spreadsheet読込->json取得
var sheet = getSheet(URL_BOOK, SHEETNAME);
var json = convertSheet2Json(sheet);
var calendar = CalendarApp.getCalendarById(CALENDAR_ID);
for(var i=0; i<json.length; i++) {
var event = json[i];
var title = event.title;
var date = event.date;
var options = {description: event.description};
calendar.createAllDayEvent(title, date, options);
}
}
function getSheet(bookUrl, sheetName) {
var book = SpreadsheetApp.openByUrl(bookUrl);
return book.getSheetByName(sheetName);
}
// https://gist.github.com/daichan4649/8877801#file-convertsheet2json-gs
function convertSheet2Json(sheet) {
// first line(title)
var colStartIndex = 1;
var rowNum = 1;
var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
var firstRowValues = firstRange.getValues();
var titleColumns = firstRowValues[0];
// after the second line(data)
var lastRow = sheet.getLastRow();
var rowValues = [];
for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) {
var colStartIndex = 1;
var rowNum = 1;
var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
var values = range.getValues();
rowValues.push(values[0]);
}
// create json
var jsonArray = [];
for(var i=0; i<rowValues.length; i++) {
var line = rowValues[i];
var json = new Object();
for(var j=0; j<titleColumns.length; j++) {
json[titleColumns[j]] = line[j];
}
jsonArray.push(json);
}
return jsonArray;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.