Sunday 24 April 2016

Sending data to Google docs from your web page

google spread Sheets as DB: 
=======================
Google Sheets as a Database – INSERT with Apps Script using POST/GET methods (with ajax eg)
-----------------------------------------------------------------------------------------------------------------------

goto Google.drive.com

Click->New-> google sheets

==> open new untitled spreadsheet

Name on your own ( Presale Email Subscribers)

Enter fields you want to store from Form

==> eg .timestamp, name, Email, phone,etc....


In Menu(sheet) ->Tools->Script Editor click

It open new Gogole app script editor with code.gs fine name

Enter project name ( Form Script)

Mention Sheet name in this script is mandatory. Eg. Sheet1

Enter the below code

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

//  2. Run > setup
//
//  3. Publish > Deploy as web app
//    - enter Project Version name and click 'Save New Version'
//    - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
//  4. Copy the 'Current web app URL' and post this in your form/script action
//
//  5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

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

// If you don't want to expose either GET or POST methods you can comment out the appropriate function
//function doGet(e){
//  return handleResponse(e);
//}

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

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());
}

Make to doPost() or doGet()


Click on the Save icon. Set the dropdown in the nav bar to 'setup’ and click on the right-pointing triangle to its left to run this function. It should show 'Running function setup’ and then put up a dialog 'Authorization Required’. Click on Continue. In the next dialog 'Request for permission - Formscript would like to’ click on Accept.

In the menus click on File > Manage Versions… We must save a version of the script for it to be called. In the box labeled 'Describe what has changed’ type 'Initial version’ and click on 'Save New Version’, then on 'OK’.

Back to the menus: click on Resources > Current roject’s triggers. In this dialog click on 'No triggers set up. Click here to add one now’. In the dropdowns select 'doPost’, 'From spreadsheet’, and 'On form submit’, then click on 'Save’.

Back to the menus: click on Publish > Deploy as web app… For 'Who has access to the app:’ select 'Anyone, even anonymous’. Leave 'Execute the app as:’ set to 'Me’ and Project Version to '1’. Click the 'Deploy’ button.

A dialog should appear announcing 'This project is now deployed as a web app’. Copy the Current web app URL from the dialog; it should look something like:

https://script.google.com/macros/s/AKfycbw6RTOxn5OT_BIw9Nl_3KoFSXEQEbiKSZCLyombb1YqkGfRKUSz/exec
Click OK.

Now go back to google-sheet.js and replace 'SCRIPT URL GOES HERE’ with the URL copied from the dialog.

Display or refresh the index.html web page. Enter data into the four fields and click on the 'Send’ button. Within a few seconds that data should appear in your Google sheet. If your browser is Google Chrome, right-click in the web page and click on Inspect Element > Console. It should show:

Hooray, it worked!
- Object
success
- Object
Well done! You can now modify this form and drop it into any web page to collect–at no cost–responses from those who visit your page. With a little research and effort you may be able to get Google Apps to email every time someone submits your form.

I plan to use it to collect names and email addresses of those who visit an MVP page, where the “product” is just a description of the SaaS site I have in mind. I may also use it on a stand-alone blog where I do not want to let readers post comments, but I would rather they send them to me so I can screen, remove spam, edit, and comment on them before putting them up.



full code for index.html and sheet.js
---------------------------------------


<!DOCTYPE html>
<html lang='en'>
  <head>
    <meta charset='utf-8'>
    <meta content='IE=edge' http-equiv='X-UA-Compatible'>
    <meta content='width=device-width, initial-scale=1' name='viewport'>
  </head>
  <body>
    <!-- Contact Form - sent to a Google Sheet -->
    <form id='foo'>
      <p>
        <label>Name</label>
        <input id='name' name='name' type='text'>
      </p><p>
        <label>Email Address</label>
        <input id='email' name='email' type='email'>
      </p><p>
        <label>Phone Number</label>
        <input id='phone' name='phone' type='tel'>
      </p><p>
        <label>Message</label>
        <textarea id='message' name='message' rows='5'></textarea>
      </p>
        <div id='success'></div>
        <button type='submit'>Send</button>
    </form>

  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
  <!-- Custom Theme JavaScript -->
  <script>

// Variable to hold request
var request;

// Bind to the submit event of our form
$("#foo").submit(function(event){

    // Abort any pending request
    if (request) {
        request.abort();
    }
    // setup some local variables
    var $form = $(this);

    // Let's select and cache all the fields
    var $inputs = $form.find("input, select, button, textarea");

    // Serialize the data in the form
    var serializedData = $form.serialize();

    // Let's disable the inputs for the duration of the Ajax request.
    // Note: we disable elements AFTER the form data has been serialized.
    // Disabled form elements will not be serialized.
    $inputs.prop("disabled", true);

    // Fire off the request to /form.php
    request = $.ajax({
        url: "https://script.google.com/macros/s/AKfycbxNDXmWaInWS-aHhBB8XgSbxjR-kk0UI0C05g3pUQ2G8KghHPU/exec",
        type: "post",
        data: serializedData
    });

    // Callback handler that will be called on success
    request.done(function (response, textStatus, jqXHR){
        // Log a message to the console
        console.log("Hooray, it worked!");
        console.log(response);
        console.log(textStatus);
        console.log(jqXHR);
alert("success");

location.reload();
    });

    // Callback handler that will be called on failure
    request.fail(function (jqXHR, textStatus, errorThrown){
        // Log the error to the console
        console.error(
            "The following error occurred: "+
            textStatus, errorThrown
        );
    });

    // Callback handler that will be called regardless
    // if the request failed or succeeded
    request.always(function () {
        // Reenable the inputs
        $inputs.prop("disabled", false);
    });

    // Prevent default posting of form
    event.preventDefault();
});

</script>
</html>

No comments:

Post a Comment