当前位置:首页 > 未分类 > 正文内容

Publish Google Spreadsheets as JSON with Apps Script

ceacer3周前 (05-02)未分类9726

The previous snippet on getting Spreadsheets as JSON required you to make a sheet public and also publish as HTML before other apps can pull data as JSON from the sheet.

If you would not like to make your Google spreadsheet public, you can use Google Apps Script to create a web app that will expose the sheet data as JSON.

/* Source: https://gist.github.com/daichan4649/8877801 */
function doGet(e) {

  var sheetName = "Sheet 1";
  var sheetId   = "1234...";

  var book = SpreadsheetApp.openById(sheetId);
  var sheet = book.getSheetByName(sheetName);

  var json = convertSheet2JsonText(sheet);

  return ContentService
          .createTextOutput(JSON.stringify(json))
          .setMimeType(ContentService.MimeType.JSON);
}

function convertSheet2JsonText(sheet) {
  // first line(title)
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) {
    var colStartIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
  }

  // create json
  var jsonArray = [];
  for(var i=0; i
    var line = rowValues[i];
    var json = new Object();
    for(var j=0; j
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  return jsonArray;
}

相关文章

How to Use Conditional Logic in Google Documents

How to Use Conditional Logic in Google Documents

Conditional content allows you to customize your Google Docs template and generate different version...

Formulas in Google Sheets Disappear When New Rows Are Added

Formulas in Google Sheets Disappear When New Rows Are Added

An order form, created in Google Forms, requires customers to provide their full name, the item quan...

Send WhatsApp Messages with a Google Sheets Function

Send WhatsApp Messages with a Google Sheets Function

In a previous tutorial, you learned how to send WhatsApp messages from Google Sheets using the offic...

How to Use Conditional Formatting in Google Sheets to Highlight Information

How to Use Conditional Formatting in Google Sheets to Highlight Information

Conditional formatting in Google Sheets makes it easy for you to highlight specific cells that meet...

How to Send SMS Messages with Google Sheets and Android Phone

How to Send SMS Messages with Google Sheets and Android Phone

The Mail Merge for Gmail add-on lets you send personalized emails via Gmail but wouldn’t it be nice...

How to Extract URLs from HYPERLINK Function in Google Sheets

The HYPERLINK formula of Google Sheets lets you insert hyperlinks into your spreadsheets. The functi...