Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
documentCloud helper google script

Document Cloud Helper for google spreadsheets made with google scripts

Template link

/***********************************************************************************
// Author: Juan Elosua (Twitter: @jjelosua)
// Description: Google Apps Script associated with Doc2Media spreadsheet template
// to get Data From DocumentCloud and prepopulate data in the required final sheet format.
************************************************************************************/
var DC_SHEET = "doc2Media";
var DC_URL_HEADER_CELLS = "A1:B1";
var DC_URL_CELL = "A2";
var COMMENTS_CELL = "B2";
var DC_URL_HEADERS = [["DocumentCloud URL", "Comments"]];
var doc2media_COMMENT = "Copy the URL from the document cloud document here\nExample: https://www.documentcloud.org/documents/1678993-garrido.html";
var doc2media_HEADERS_RANGE = "A1:I1";
var doc2media_HEADERS = [["page","annotation","annotation_title", "tag","index_text","media_label","media_type", "media_source", "media_html_content"]];
// Create a custom menu to access the script functionality
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('GetDocumentCloudData')
.addItem('Get Data', 'get_dc_data')
.addSeparator()
.addItem('Enter data manually', 'manual')
.addItem('Reset Sheet', 'reset')
.addToUi();
}
function get_dc_data() {
// Get the parameters sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dc_sheet = ss.getSheetByName(DC_SHEET);
var site_url = dc_sheet.getRange(DC_URL_CELL).getValue();
var url = site_url.toString().replace(".html",".json");
var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var data = JSON.parse(json);
var annotations
clear_sheet();
//Add header
dc_sheet.getRange(doc2media_HEADERS_RANGE)
.setValues(doc2media_HEADERS)
.setBackgroundRGB(0, 0, 0)
.setHorizontalAlignment("center")
.setFontColor("white")
.setFontWeight("bold");
var annotations = data.annotations;
for (var i in annotations) {
dc_sheet.appendRow([annotations[i].page, annotations[i].id, annotations[i].title]);
}
}
function clear_sheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dc_sheet = ss.getSheetByName(DC_SHEET);
dc_sheet.clear();
}
function reset() {
clear_sheet();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dc_sheet = ss.getSheetByName(DC_SHEET);
//Restore headers
dc_sheet.getRange(DC_URL_HEADER_CELLS)
.setValues(DC_URL_HEADERS)
.setBackgroundRGB(0, 0, 0)
.setHorizontalAlignment("center")
.setFontColor("white")
.setFontWeight("bold");
//Restore comment
dc_sheet.getRange(COMMENTS_CELL)
.setValue(doc2media_COMMENT);
}
function manual() {
clear_sheet();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dc_sheet = ss.getSheetByName(DC_SHEET);
dc_sheet.getRange(doc2media_HEADERS_RANGE)
.setValues(doc2media_HEADERS)
.setBackgroundRGB(0, 0, 0)
.setHorizontalAlignment("center")
.setFontColor("white")
.setFontWeight("bold");
dc_sheet.autoResizeColumn(9);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment