使用AJAX将表单数据提交到Google工作表_Ajax_Google Apps Script_Google Sheets_Cors

使用AJAX将表单数据提交到Google工作表

ajax google-apps-script google-sheets cors

使用AJAX将表单数据提交到Google工作表,ajax,google-apps-script,google-sheets,cors,Ajax,Google Apps Script,Google Sheets,Cors,我正在尝试使用AJAX将表单数据提交到GoogleSheet 我的HTML表单代码如下所示 <form id="test-form"> <input type="text" name="1" /> <div class="question"> <button type="submit" id="submit-form" class=&q

我正在尝试使用AJAX将表单数据提交到GoogleSheet

我的HTML表单代码如下所示

<form id="test-form">
<input type="text" name="1" />
<div class="question">
<button type="submit" id="submit-form" class="activebtn showElement">
      Submit
</button>
</div>
</form>

var $form = $('form#test-form');
var url = 'https://script.google.com/macros/s/AKfycbyC_o4JsVtdevTrjeBOzSi6HJEJjzTzY/exec';

$('#submit-form').on('click', function (e) {
    e.preventDefault();
    $.ajax({
        url: url,
        method: "GET",
        dataType: 'jsonp',
        contentType: "application/javascript",
        data: $form.serializeObject(),
        crossDomain: true,
        headers: {
            'Content-Type': 'text/plain;charset=utf-8',
            'Access-Control-Allow-Origin': '*'
        },
        success: function (result) {
            console.log(result);
        },
        error: function (result) {
            console.log(result);
        }
    });
});

function doGet(e){
  return handleResponse(e);
}

//  Enter sheet name where data is to be written below
var SHEET_NAME = "Sheet1";

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
  
  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);
    
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(e.parameter[headers[i]]);
      }
    }
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}
});

我的谷歌应用程序脚本代码如下

<form id="test-form">
<input type="text" name="1" />
<div class="question">
<button type="submit" id="submit-form" class="activebtn showElement">
      Submit
</button>
</div>
</form>

var $form = $('form#test-form');
var url = 'https://script.google.com/macros/s/AKfycbyC_o4JsVtdevTrjeBOzSi6HJEJjzTzY/exec';

$('#submit-form').on('click', function (e) {
    e.preventDefault();
    $.ajax({
        url: url,
        method: "GET",
        dataType: 'jsonp',
        contentType: "application/javascript",
        data: $form.serializeObject(),
        crossDomain: true,
        headers: {
            'Content-Type': 'text/plain;charset=utf-8',
            'Access-Control-Allow-Origin': '*'
        },
        success: function (result) {
            console.log(result);
        },
        error: function (result) {
            console.log(result);
        }
    });
});

function doGet(e){
  return handleResponse(e);
}

//  Enter sheet name where data is to be written below
var SHEET_NAME = "Sheet1";

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
  
  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);
    
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(e.parameter[headers[i]]);
      }
    }
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}
});

我面临

跨源读取阻塞（CORB）跨源阻塞问题

Web应用发布设置
这是因为您尚未授权脚本。您需要做的是，转到脚本编辑器，在那里您已经编写了doGet函数，然后从那里运行它。然后它将要求您授权脚本
还请注意，当您对脚本进行任何更改并保存代码时，它将无法正常工作，直到您保存新版本并发布它。
我看不到任何授权标题。您是通过网站还是网络应用程序进行此操作？如果是通过网站，我认为您可能需要设置一个服务帐户。您是否加载了jquery？请提供好的信息，现在是学习跨域的好时机：在ajax调用中为true您用于将脚本发布为web应用程序的设置是什么？谢谢@Anees Hameed。我是谷歌应用程序脚本的新手。你能引导我遵循你的建议吗？