Generate localizable strings with Google Spreadsheets and Twine

localizable_google_spreadsheets_and_twine.md

1. Create a Google spreadsheet like the following example:

KEY	EN	ES	CA	TAGS	COMMENTS
// Core
core_accept	Accept	Aceptar	D'acord	android,ios,core	-
core_cancel	Cancel	Cancelar	Cancel·lar	android,ios,core	-
core_ok	OK	OK	OK	ios,core	-
// Login
login_email_hint	Enter your email	Introduce tu email	Introdueix el teu email	android,ios,login	-
login_pasword_hint	Enter your password	Intruduce tu contraseña	Introdueix la teva contrasenya	android,ios,login	-

2. Follow the Twine format on the language translations columns.
3. Create a script with the snippet code.
4. Open the spreadsheet.
5. Select the tab with the localized strings.
6. Select the "Localization" menu option at the left of the "Help" menu option.
7. Select "Copy and Export" option.
8. Copy and paste the content on your twine file.
9. If you prefer to download the file generated, go to your spreadsheet folder. On this folder, you can see the "temp" folder where you can download the Twine file generated.

Clarifications:

• Sections are marked on "KEY" spreadsheet column like a comment "//".
• Empty translation cells are generated empty.
• You can generate inclusive and exclusive localized strings using tags with Twine (read the documentation).
• Comments are not included in generated Twine file/content.

How to add a new language:

• Add the new language column between the last language and "TAGS" column on the spreadsheet.
• Go to the spreadsheet script.
• Update "COLUMNS" variable with the same order of spreadsheet.
• Update the "COLUMN_POSITION_TAGS" and "COLUMN_POSITION_COMMENTS" position.
• Save the script.
• You are ready!

Google Spreadsheets script:

// **************************************************************************
/* 
 *  VARS TO UPDATE when a new language is added
 * 
 *  MANDATORY for COLUMNS var: 
 *  "KEY" always the first column and "TAGS" and "COMMENTS" the last columns
 */
var COLUMNS = ["key", "en", "es", "ca", "tags", "comments"];
var COLUMN_POSITION_TAGS = 4;
var COLUMN_POSITION_COMMENTS = 5;
// **************************************************************************

var COLUMN_POSITION_KEY = 0;
var TEMP_FOLDER_NAME = "temp";
var TWINE_FILENAME = "twine.txt";
var MARKER_SECTION = "//"
var KEYWORD_TWINE_TAGS = "tags";
var FILE_CONTENT = ""


function onOpen() {
    var app = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [{
        name: "Copy and Export",
        functionName: "init"
    }];
    app.addMenu("Localization", menuEntries);
}

function init() {
    try {
        var sheetData = getData();
        if (checkColumns(sheetData)) {
            var isFileParsed = parseFileContent(sheetData)
            if (isFileParsed) {
                var filename = createFile(FILE_CONTENT);
                copyAndExportDialog(FILE_CONTENT);
            }
        }
    } catch (error) {
        Logger.log(error);
        showErrorDialog(error);
    }
}

function getData() {
    var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = activeSpreadsheet.getActiveSheet();

    var activeRange = sheet.getDataRange();
    var data = activeRange.getValues();
    return data;
}

function checkColumns(fileData) {
    if (fileData[0][0].toLowerCase() == COLUMNS[0] &&
        fileData[0][1].toLowerCase() == COLUMNS[1] &&
        fileData[0][2].toLowerCase() == COLUMNS[2] &&
        fileData[0][3].toLowerCase() == COLUMNS[3] &&
        fileData[0][4].toLowerCase() == COLUMNS[4] &&
        fileData[0][5].toLowerCase() == COLUMNS[5]
    ) {
        return true;
    }

    var errorColumns = "<p><b>Do you have selected the tab with the localized strings?</b></p>";
    errorColumns += "<p>If the error persist, check the columns names on spreadsheet or update the script variables.</p>";
    showErrorDialog(errorColumns);
    Logger.log(errorColumns);
    return false;
}

function parseFileContent(fileData) {
    var i;
    var j;
    var numberOfRows = fileData.length;
    var numberOfColumns = COLUMNS.length;

    // We skipt postion 0 because this row has the headers
    for (i = 1; i < numberOfRows; i++) {
        for (j = 0; j < numberOfColumns; j++) {
            var columnName = COLUMNS[j]
            var cell = fileData[i][j].trim();

            // skip empty cell        
            if (j === 0 && cell === "") {
                break;
            }

            if (j === 0 && cell.indexOf(MARKER_SECTION) == 0) { // section
                addSeparator();
                addSection(cell.replace(MARKER_SECTION, "").trim());
                break;

            } else {
                if (j === COLUMN_POSITION_KEY && cell != "") { // key
                    addKey(cell)
                } else if (j === COLUMN_POSITION_TAGS && cell != "") { // tags
                    addTags(cell);
                } else if (j === COLUMN_POSITION_COMMENTS) { // comments
                    // Avoid comments
                } else { // translations
                    addTranslation(columnName, cell);
                }
            }
        }
    }

    Logger.log("Twine content:" + FILE_CONTENT);
    return true;
}

function addSection(sectionName) {
    FILE_CONTENT += "[[" + sectionName + "]]\n";
}

function addKey(keyName) {
    FILE_CONTENT += "\t[" + keyName + "]\n";
}

function addTranslation(language, translation) {
    FILE_CONTENT += "\t\t" + language + " = " + translation + "\n";
}

function addTags(tags) {
    FILE_CONTENT += "\t\t" + KEYWORD_TWINE_TAGS + " = " + tags + "\n";
}

function addSeparator() {
    FILE_CONTENT += "\n";
}

function createFile(content) {
    var thisFileId = SpreadsheetApp.getActive().getId();
    var thisFile = DriveApp.getFileById(thisFileId);
    var parentFolder = thisFile.getParents().next();
    var isTempFolderCreated = getFolder(parentFolder, TEMP_FOLDER_NAME);

    if (isTempFolderCreated === false) {
        parentFolder.createFolder(TEMP_FOLDER_NAME);
        Logger.log("Temp folder created");
    } else {
        removeFolder(parentFolder, TEMP_FOLDER_NAME);
        Logger.log("Temp folder removed");
        parentFolder.createFolder(TEMP_FOLDER_NAME);
        Logger.log("Temp folder created");
    }

    var tempFolder = getFolder(parentFolder, TEMP_FOLDER_NAME);
    var fileName = getFileName(new Date())
    tempFolder.createFile(fileName, content);
    Logger.log("File created");

    return fileName
}

function getFolder(parentFolder, folderName) {
    var folders = parentFolder.getFolders();
    while (folders.hasNext()) {
        var folder = folders.next();
        if (folderName == folder.getName()) {
            return folder;
        }
    }
    return false;
}

function removeFolder(parentFolder, folderName) {
    var folders = parentFolder.getFolders();
    while (folders.hasNext()) {
        var folder = folders.next();
        if (folderName == folder.getName()) {
            parentFolder.removeFolder(folder)
            return true;
        }
    }
    return false;
}

function getFileName(d) {
    var fileName = d.getFullYear();

    fileName += "_"
    if (d.getMonth() + 1 < 10) {
        fileName += '0' + (d.getMonth() + 1)
    } else {
        fileName += '' + (d.getMonth() + 1)
    }

    fileName += "_"
    if (d.getDate() < 10) {
        fileName += '0' + d.getDate()
    } else {
        fileName += '' + d.getDate()
    }

    fileName += "__"
    if (d.getHours() < 10) {
        fileName += '0' + d.getHours()
    } else {
        fileName += '' + d.getHours()
    }

    fileName += "_"
    if (d.getMinutes() < 10) {
        fileName += '0' + d.getMinutes()
    } else {
        fileName += '' + d.getMinutes()
    }

    fileName += "_" + TWINE_FILENAME
    return fileName;
}

function copyAndExportDialog(content) {
    var width = 700;
    var height = 500;
    var messageHeight = (height - 60) + "px";
    var twineLink = "<a href=\"https://github.com/scelis/twine\" target=\"_blank\">Twine</a>";
    var message = "<p>Use " + twineLink + " command line tool to manage your strings and their translations.</p>";
    message += "<textarea readonly style=\"width:100%;height:" + messageHeight + ";\">" + content + "</textarea>";
    var html = HtmlService
        .createHtmlOutput(message)
        .setWidth(width)
        .setHeight(height);
    SpreadsheetApp.getUi().showModalDialog(html, '📝 Copy and Export');
}

function showErrorDialog(error) {
    var errorMessage = "<p>" + error + "</p>"
    showDialog("ERROR", errorMessage)
}

function showDialog(title, message) {
    var htmlOutput = HtmlService
        .createHtmlOutput(message)
        .setWidth(300)
        .setHeight(250);
    SpreadsheetApp.getUi().showModalDialog(htmlOutput, title);
}

This is great! Would be great to see an "Import" from Twine option.

Thank you, this is great! Not sure if there is a way to create a PR for a Gist, but a few tweaks (careful, I don't know Google App Scripts so this might not be the best code possible)

In order to be able to put "pt-PT" for instance as a language, without seeing an error, I had to do the following:

Before

 (fileData[0][0].toLowerCase() == COLUMNS[0] &&
        fileData[0][1].toLowerCase() == COLUMNS[1] &&
        fileData[0][2].toLowerCase() == COLUMNS[2] &&
        fileData[0][3].toLowerCase() == COLUMNS[3] &&
        fileData[0][4].toLowerCase() == COLUMNS[4] &&
        fileData[0][5].toLowerCase() == COLUMNS[5]

After

 (fileData[0][0].toLowerCase() == COLUMNS[0].toLowerCase() &&
        fileData[0][1].toLowerCase() == COLUMNS[1].toLowerCase() &&
        fileData[0][2].toLowerCase() == COLUMNS[2].toLowerCase() &&
        fileData[0][3].toLowerCase() == COLUMNS[3].toLowerCase() &&
        fileData[0][4].toLowerCase() == COLUMNS[4].toLowerCase() &&
        fileData[0][5].toLowerCase() == COLUMNS[5].toLowerCase()

Small tweaks to reduce the number of edits we have to do when adding languages, IF the tags and comments are always at the end:

var COLUMN_POSITION_TAGS = COLUMNS.length - 2;
var COLUMN_POSITION_COMMENTS = COLUMN_POSITION_TAGS + 1;

Also, comments are super useful, if anyone needs them:

Before

} else if (j === COLUMN_POSITION_COMMENTS) { // comments
    // Avoid comments
}

After

} else if (j === COLUMN_POSITION_COMMENTS) { // comments
    addComment(cell);
}

and add somewhere

function addComment(comment) {
    FILE_CONTENT += "\t\t" + KEYWORD_COMMENT_TAGS + " = " + comment + "\n";
}

Thanks @KevinQuisquater, when I have time I will check it, but adding comments on it is very helpful for anyone.