기존의 https://docs.google.com/spreadsheets/d/1W9HOGd0qWNtLpvgKzsrT0Gf6RlKxrknHUVULRooNYJg/gviz/tq?tqx=out:json 은 제대로 된 json 데이타를 리턴하지 않게 됬다. 이유는 알 수 없지만, 일단 이 api가 시트를 json으로 컨버트하는 용도는 아니라는 것은 확실하다. 추측하기론 차트를 보여주기 위해서 데이타를 가공하면서 헤더를 임의로 분석해서 판단하는 것 같다. 데이타가 계속 입력됨에 따라 헤더가 잘 못 판단된 응답을 내려주는데 해결할 방법을 찾지 못했다.
내보내기 중에 CSV 방식은 간단하기는 하지만 엔터(line break)가 있는 데이타는 제대로 표시하지 못하는 문제가 있다. xlsx는 너무 복잡하고 xml이나 json포맷으로 export를 지원한다면 활용도가 높아질텐데 왜 기능이 없는지 모르겠다.
특정 스프레드시트에 데이타를 넣고 조회하는 web api를 만드는 방법을 소개하고 있다. api의 구현은 자바스크립트이고 예제도 어렵지 않아서 구현을 실제로 해 보았다.
var SHEET_NAME = "sheet1";
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
return setResponse(e);
}
function doPost(e){
// do nothing
}
function setResponse(e) {
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
try {
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var line = sheet.getLastRow();
var data = new Array();
for(var j=1;j<=line;j++){
var values = sheet.getRange(j, 1, 1,sheet.getLastColumn()).getValues();
data.push(values[0]);
}
var total = new Object();
total.code = "success";
total.result = data;
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"code":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
return ContentService
.createTextOutput(JSON.stringify(total))
.setMimeType(ContentService.MimeType.JSON);
}
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
배포된 web api를 브라우저에서 호출했더니 시트 전체 데이타가 json으로 내려왔다. 다만 시간이 너무 오래걸렸다. 한 30-40초 정도 걸렸는데, 산책하면서 생각해보니 ROW갯수만큼 호출해서 그런것 같았다. API를 뒤져보니 해결책은 간단했다. 모든 ROW를 한 번에 가져오면 된다. 애초에 ROW하나를 가져오는데 Array안에 Array가 있어서 왜 그런가 했더니 그 의문이 풀렸다. 샘플만 보고 api 명세를 안 보면 생기는 문제다.
https://developers.google.com/apps-script/reference/spreadsheet/sheet
getRange( | Range | Returns the range with the top left cell at the given coordinates with the given number of rows and columns. |
//var data = new Array();
//for(var j=1;j<=line;j++){
// var values = sheet.getRange(j, 1, 1,sheet.getLastColumn()).getValues();
// data.push(values[0]);
//}
var total = new Object();
total.code = "success";
total.result = sheet.getRange(1, 1, line, sheet.getLastColumn()).getValues();
이렇게 수정했더니 2초만에 데이타를 가져오는데 성공했다.
JSON포맷이 더 간단해 졌으니 LanguageDownloader를 다시 수정했다.
typealias LanguageDownloaderCallback = (result: OptionalResult<String>) -> Unit
class LanguageDownloader {
companion object {
private const val JSON_EXPORT_URL =
"https://script.google.com/macros/s/------------------------------/exec"
private const val JSON_FILENAME = "languages.json"
private val lock = Any()
private val httpClient = OkHttpClient().apply { setReadTimeout(30, TimeUnit.SECONDS) }
}
private val languageToColumnMap = mutableMapOf<String, Int>() // "en" to 4
private val columnToLanguageMap = mutableMapOf<Int, MutableMap<String, String>>() // 4 to Strings
private val languageList = mutableListOf<String>() // "en", "ko", ...
private val columnToFriendlyNameMap = mutableMapOf<Int, String>() // 4 to "English"
private val friendlyNameMap = mutableMapOf<String, String>() // "en" to "English", "ko" to "Korean"
fun downloadSheet(callback: LanguageDownloaderCallback) {
if (!BuildConfig.RESTRING) {
return
}
val request = Request.Builder()
.url(JSON_EXPORT_URL)
.build()
httpClient.newCall(request).enqueue(object : Callback {
override fun onFailure(request: Request?, e: IOException?) {
ToastUtils.show(e?.message)
callback(OptionalResult(e))
}
override fun onResponse(response: Response?) {
try {
val bodyString = response?.body()?.string()
?: throw RuntimeException("Download failed: google spreadsheet json file")
parseBodyString(bodyString)
callback(OptionalResult(bodyString))
} catch (ex: Exception) {
ToastUtils.show(ex.message)
callback(OptionalResult(ex))
}
}
})
}
fun downloadSheetSync(): OptionalResult<String> {
if (!BuildConfig.RESTRING) {
return OptionalResult("")
}
val request = Request.Builder()
.url(JSON_EXPORT_URL)
.build()
return try {
val bodyString = httpClient.newCall(request).execute().body()?.string()
?: throw RuntimeException("Download failed: google spreadsheet json file")
parseBodyString(bodyString)
OptionalResult(bodyString)
} catch (ex: Exception) {
OptionalResult(ex)
}
}
fun parseBodyString(bodyString: String) {
if (!BuildConfig.RESTRING) {
return
}
val jsonObject = JSONObject(bodyString)
if (jsonObject.getString("code") != "success") {
throw RuntimeException(jsonObject.getString("Invalid server response.. Retry again."))
}
val rowsArray = jsonObject.getJSONArray("result")
loop@ for (row in 0 until rowsArray.length()) {
val colsArray = rowsArray.getJSONArray(row)
var stringId: String? = null
for (col in 0 until colsArray.length()) {
val text = colsArray.getString(col)
if (col == 0) {
if (text == "[common]" || text == "[app]") {
continue
}
if (text == "--end--") {
break@loop
}
stringId = text
}
if (stringId == null || stringId == "" || col < 4) {
continue
}
if (text == null || text == "") {
continue
}
when (stringId) {
"Language Name" -> columnToFriendlyNameMap[col] = text
"Language Id" -> {
val language = text
languageToColumnMap[language] = col
columnToLanguageMap[col] = HashMap()
languageList.add(language)
columnToFriendlyNameMap[col]?.let { friendlyNameMap[language] = it }
}
else -> columnToLanguageMap[col]?.put(stringId, touchString(text))
}
}
}
}
private fun touchString(text: String): String {
if (!BuildConfig.RESTRING) {
return text
}
return text.replace("\${str}", "%s")
.replace("\${str1}", "%1\$s")
.replace("\${str2}", "%2\$s")
.replace("\${str3}", "%3\$s")
.replace("\${str4}", "%4\$s")
.replace("\${num}", "%d")
.replace("\${num1}", "%1\$d")
.replace("\${num2}", "%2\$d")
.replace("\${num3}", "%3\$d")
.replace("\${num4}", "%4\$d")
}
fun getLanguageList(): MutableList<String> {
return languageList
}
fun getStrings(language: String): MutableMap<String, String>? {
languageToColumnMap[language]?.let { column ->
return columnToLanguageMap[column]
}
return null
}
fun readCache(context: Context): String? {
synchronized(lock) {
val file = File(context.filesDir, JSON_FILENAME)
return FileUtils.readFileContent(file)
}
}
fun writeCache(context: Context, content: String) {
synchronized(lock) {
val file = File(context.filesDir, JSON_FILENAME)
FileUtils.writeFileContent(file, content)
}
}
fun getFriendlyName(language: String): String? = friendlyNameMap[language]
}
