Advertisement
Murlogue

CopySpreadsheetTableToDocument.js

Jul 5th, 2013
8,345
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*global SpreadsheetApp: false, Browser: false, DocumentApp: false, Logger: false*/
  2.  
  3. /*
  4. * Written by: mick@javascript-spreadsheet-programming.com
  5. *
  6. * Date: 2013-07-05
  7. *
  8. * Passed by JSLint
  9. */
  10.  
  11. /**
  12. * Copy array values to a document table
  13. *
  14. * Given a document name and an array of arrays, creates the document table.
  15. * Highlights the first row (header) in bold.
  16. * Checks for two arguments, the first must be a string and the second an array.
  17. *
  18. * @param {type} docName
  19. * @param {type} values
  20. * @returns {undefined}
  21. */
  22. function writeTableToDocument(docName, values) {
  23.     "use strict";
  24.     var doc,
  25.         table,
  26.         headerRow,
  27.         styles = {};
  28.     if (typeof docName !== 'string') {
  29.         throw {name: 'TypeError',
  30.             message: 'Function writeTableToDocument() ' +
  31.                     'expects a string name for the new ' +
  32.                     'document as its first argument!'};
  33.     }
  34.     if (!Array.isArray(values)) {
  35.         throw {name: 'TypeError',
  36.             message: 'Function writeTableToDocument() ' +
  37.                     'expects an array of values as its second argument.'};
  38.     }
  39.     try {
  40.         doc = DocumentApp.create(docName);
  41.         table = doc.getBody().appendTable(values);
  42.         headerRow = table.getRow(0);
  43.         styles[DocumentApp.Attribute.BOLD] = true;
  44.         headerRow.setAttributes(styles);
  45.         doc.saveAndClose();
  46.     } catch (ex) {
  47.         throw ex;
  48.     }
  49. }
  50.  
  51. /**
  52. * Return the cell values of a named range
  53. * checks that the given argument is type string.
  54. * Uses this name to reference a spreadsheet range.
  55. * If the range name does not exists, it will return 'undefined'
  56. *
  57. * @param rngName string
  58. * @returns array
  59. */
  60. function getRangeNameValues(rngName) {
  61.     "use strict";
  62.     var ss = SpreadsheetApp.getActiveSpreadsheet(),
  63.         namedRng,
  64.         namedRngValues = [];
  65.     if (typeof rngName !== 'string') {
  66.         throw {name: 'TypeError',
  67.             message: 'Function getRangeNameValues() ' +
  68.                     'expects a single string argument for the ' +
  69.                     'name of the target range!'};
  70.     }
  71.     try {
  72.         namedRng = ss.getRangeByName(rngName);
  73.         namedRngValues = namedRng.getValues();
  74.         return namedRngValues;
  75.     } catch (ex) {
  76.         throw ex;
  77.     }
  78. }
  79.  
  80.  
  81. /**
  82. * Runs the copying code
  83. *
  84. * @returns {undefined}
  85. */
  86. function main() {
  87.     "use strict";
  88.     var rngName = "ContactDetails",
  89.         docName = rngName,
  90.         rngValues;
  91.     try {
  92.         rngValues = getRangeNameValues(rngName);
  93.         writeTableToDocument(docName, rngValues);
  94.         Browser.msgBox('New file created');
  95.     } catch (ex) {
  96.         Browser.msgBox('There has been an error, check the log');
  97.         Logger.log('ERROR:');
  98.         Logger.log(ex.message);
  99.         throw ex;
  100.     }
  101. }
Advertisement
RAW Paste Data Copied
Advertisement