Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
AppScript to copy Google Spreadsheet into Treasure Data
CONSTANTS = {
maxColumns: 1000,
rowsPerRead: 200,
maxRows: 100000,
tdAPIKey: 'YOUR_API_KEY',
databaseName: undefined,
tableName: undefined,
timeColName: "time",
timeLowerBound: Date.parse(new Date())/1000 - 7*86400,
timeUpperBound: Date.parse(new Date())/1000 + 3*86400
}
function toTDEntityName(name) {
return name.toLowerCase().replace(/[^a-z0-9_]/g, '_').replace(/_+/, '_');
}
function postTreasureData(events, database, table, apikey) {
var data = {};
data[database+"."+table] = events;
var payload = JSON.stringify(data);
Logger.log(payload);
var options = {
"method": "POST",
"contentType" : "application/json",
"headers" : {
"X-TD-Write-Key": apikey,
"X-TD-Data-Type": "k"
},
"payload": payload
};
var response = UrlFetchApp.fetch("http://in.treasuredata.com/js/v3/event/", options);
Logger.log(response);
}
function readColumnNames(activeSheet) {
// Parse column names
// Assume that the table begins on A1, with the first row as the header
var colNames = [];
var colIndex = 1;
while (colIndex < CONSTANTS.maxColumns) {
var colName = activeSheet.getRange(1, colIndex).getValue();
if (colName === "") { break; }
colNames.push(colName);
colIndex += 1;
}
return colNames;
}
function validateTimeColumn(o) {
var t = o[CONSTANTS.timeColName];
if (typeof t === "number") {
// check that the time is within reason
if (t < CONSTANTS.timeLowerBound || t > CONSTANTS.timeUpperBound) {
throw new RangeError("time field = " + t.toString() + " is out of range");
}
return o; // otherwise good.
}
if (typeof t === "string" || t instanceof Date) {
var parsed_t = Date.parse(t)/1000;
if (parsed_t !== parsed_t) { // If it's NaN
throw new Error("time field = " + t.toString() + " is malformed");
}
// check that the time is within reason
if (parsed_t < CONSTANTS.timeLowerBound || parsed_t > CONSTANTS.timeUpperBound) {
throw new RangeError("time field = " + parsed_t.toString() + " is out of range");
}
o[CONSTANTS.timeColName] = parsed_t;
return o;
}
throw new Error("Bad type for " + t.toString());
}
function readMultipleRows(activeSheet, rowIndex, colNames, rowCount) {
var rowJSONs = [];
for (var offset = 0; offset < rowCount; offset++) {
// If all values are empty in the row, then we assume that it's the end
// activeSheet.getRange(rowIndex+offset, 1).getValue() === "") { break; }
var rowValues = activeSheet.getRange(rowIndex+offset, 1, 1, colNames.length).getValues();
var isEnd = true;
for (var ii in rowValues) {
if (rowValues[0][ii] !== "") {
isEnd = false;
break;
}
}
if (isEnd) { break; }
rowJSONs.push((function() {
var o = {};
for (var ii in colNames) {
o[colNames[ii]] = rowValues[0][ii];
}
validateTimeColumn(o);
return o;
})());
}
return rowJSONs;
}
function GoogleSheet2TD() {
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var databaseName = CONSTANTS.databaseName || toTDEntityName(activeSpreadsheet.getName());
var activeSheet = SpreadsheetApp.getActiveSheet();
var tableName = CONSTANTS.tableName || toTDEntityName(activeSheet.getName());
var tdAPIKey = CONSTANTS.tdAPIKey;
colNames = readColumnNames(activeSheet);
var rowJSONs;
var rowIndex = 2;
while (rowIndex < CONSTANTS.maxRows) {
rowJSONs = readMultipleRows(activeSheet, rowIndex, colNames, CONSTANTS.rowsPerRead);
if (rowJSONs.length === 0) { break; }
postTreasureData(rowJSONs, databaseName, tableName, tdAPIKey);
rowIndex += rowJSONs.length;
}
}
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.