Posted By

asimmittal on 02/06/13


Tagged

google json apps docs spreadsheets


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

db_snipplr
icecreamboyy


Google Docs Spreadsheet Rows to JSON


 / Published in: JavaScript
 

URL: http://hackonista.blogspot.com/2013/02/scripting-in-your-spreadsheets.html

This snippet takes all the rows from your google spreadsheet and turns it into JSON, which can be copied to the clipboard. You can run this as part of the Google Docs scripting environment.

  1. /*
  2.  * create a simple container that shows the string argument
  3.  * in a text box
  4. */
  5.  
  6. function dumpDataIntoUI(data){
  7.  
  8. //the white container is the app
  9. var app = UiApp.createApplication();
  10. app.setHeight(480);
  11. app.setWidth(640);
  12.  
  13. //we want a nice label with the title of the script
  14. var label = app.createLabel("All Rows to JSON");
  15. label.setStyleAttribute("font-size","24");
  16. label.setStyleAttribute("font-weight","bold");
  17. label.setStyleAttribute("padding-bottom", "25px");
  18.  
  19. //next, we will add the text box and fill it with the string argument
  20. var text = app.createTextArea();
  21. text.setHeight(400);
  22. text.setWidth(640);
  23. text.setValue(data);
  24. text.setSelectionRange(0, data.length);
  25.  
  26. //add all the widgets into the app and show it
  27. app.add(label);
  28. app.add(text);
  29. var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  30. spreadsheet.show(app);
  31. }
  32.  
  33.  
  34. /*
  35.  * function to actually grab all the rows and put them
  36.  * into a JSON string.
  37. */
  38.  
  39. function rowsToJson() {
  40.  
  41. //grab the rows from the active spreadsheet
  42. var sheet = SpreadsheetApp.getActiveSheet();
  43. var rows = sheet.getDataRange();
  44. var numRows = rows.getNumRows();
  45. var values = rows.getValues();
  46.  
  47. //the first row contains the titles or json keys
  48. var firstRowTitles = values[0];
  49. var allDataJSONString = "";
  50.  
  51. //this loop converts all the values starting from the second row to the last
  52. //into one big JSON string
  53. for(var i = 1; i < numRows; i++){
  54. var row = values[i];
  55. var rowData = "{\n";
  56. for(var j = 0; j < row.length; j++){
  57. var comma = (j == row.length - 1)?"":",";
  58. rowData += '"' + firstRowTitles[j] + '"' + ":" + '"' + row[j] + '"' + comma + "\n";
  59. }
  60.  
  61. var comma = (i == numRows-1)?"":","
  62. rowData+="}" + comma + "\n";
  63. allDataJSONString += rowData;
  64. }
  65.  
  66. //add delimiters to that string and dump into the UI
  67. dumpDataIntoUI("[\n" + allDataJSONString.toLowerCase() + "\n]");
  68. };
  69.  
  70. /**
  71.  * Adds a custom menu to the active spreadsheet, containing a single menu item
  72.  * for invoking the readRows() function specified above.
  73.  * The onOpen() function, when defined, is automatically invoked whenever the
  74.  * spreadsheet is opened.
  75.  * For more information on using the Spreadsheet API, see
  76.  * https://developers.google.com/apps-script/service_spreadsheet
  77.  */
  78. function onOpen() {
  79. var sheet = SpreadsheetApp.getActiveSpreadsheet();
  80. var entries = [
  81. {
  82. name : "All Rows to JSON",
  83. functionName : "rowsToJson"
  84. }
  85. ];
  86.  
  87. sheet.addMenu("My Scripts", entries);
  88. };

Report this snippet  

You need to login to post a comment.