README.md

Document Cloud Helper for google spreadsheets made with google scripts

Template link

doc2media_helpers.gs

/***********************************************************************************
// 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);
}