2

Is it possible to generate different versions of a document with Google Docs? Is there any programming/scripting language associated that one can specify which text to show based on e.g. a cell's value in another spreadsheet?

  • I am not familiar with the term 'circular' but I suppose it is like a variable that you fetch from a spreadsheet? Yes, I want something like that. If possible, I also want it to be conditional. e.g. if A2 == "a"; then getvalue(A3); elif A2 == "b"; then getvalue(A4) – ParaDoX Nov 6 '14 at 20:19
  • Aha, no I do need it to be a text document, not a spreadsheet. – ParaDoX Nov 6 '14 at 20:51
2

The short is yes, you can retrieve documents using API calls, manipulate and retrieve the data, and create additional documents and directories in your Google Drive.

I believe it is even possible to grab previous 'revisions' of a document, if they are saved. As you may have noticed, there is a setting on each document that, by default, is set to keeping the latest revision. Previous versions are (I think) conditionally removed based on Drive space availability.

Generally, I'll start up a Google App Script project (https://developers.google.com/apps-script/) within a Google Site https://sites.google.com .. (create a site, and select manage site, select App Script menu on the left, and then select Add Script on the top section).

There are quite a few designations as starter project apps.. what's important to note is that this App can become a Gadget for your pages in the site.

EDIT - Sample Google Script App (Script as Web App)

I'm going to include some sample code of a completed 'dealer registration' web service and form I completed for a company I briefly worked for.

This script will open and modify an excel spreadsheet. mailing a notification to a specified email address using either the user accessing the app, or the designated user (depends on your app conifguration).

It attempts to load the excel sheet (in edit mode) by id, it's a good point of reference for your question and this answer.

Disregard the 'Creates a spreedsheet inline comments'. it doesn't attempt to create the excelSpreedsheet in this implementation; you could, of course, use the existing function to create it programmatically.. I just didn't see a point to checking for the existence of the file, as I already know it exists.

you can get the ID of a spreadsheet by looking it up in your google docs, or opening it up and reading off the hash (looks like "129o71gM0zpCnp0NX-mCr_A1oSBbse8heLDK5wU2mfQM").

Code.gs

// Script-as-app template.
function doGet(request) {
  Logger.log("[doGet] request - " + JSON.stringify(request));
  return HtmlService.createTemplateFromFile("register-form.html").evaluate();
}

function doPost(formData) {
  Logger.log("[doPost] - " + JSON.stringify(formData));

  MailApp.sendEmail("YOUREMAIL@DOMAIN.com", "GoogleScriptApp Test:", JSON.stringify(formData) + "\n");

  return HtmlService.createHtmlOutput("<div>Success! " + new Date() + "</div>");
}

/*
Create Excel Sheet if it doesn't exist
Open Excel Sheet (Authorized Detail)
Add Row (Authroized Dealer Detail/Record)
Create Document (Customer Number)
Email With Document Attached
Email Sender With Document Attached
*/

var Dealer = {};

function createExcelSheet() {
var ss = SpreadsheetApp.create("SPREADSHEETNAME");  

  var sheet = ss.insertSheet("SHEETNAME");
 Logger.log("[createExcelSheet] sheet inserted");

  /*ss.appendRow({});*/

}

var DealerInfo = {
  "BusinessName": "Test Business Name",
  "BusinessType": "Repair Shop",
  "OwnerNames": "John Doe",
  "AccountInfo" : {
    "Username" : "jdoe1",
    "Password" : "testpass1234",
    "Email": "jdoe@domain.net"
  },
  "Address" : {
    "Billing" : {
      "Line1" : "",
      "Line2" : "",
      "State" : "Louisiana",
      "Country" : "USA",
      "City" : "Mandeville",
      "Postal" : "70471" 
    },
    "Shipping" : {    
      "Line1" : "",
      "Line2" : "",
      "State" : "Louisiana",
      "Country" : "USA",
      "City" : "Mandeville",      
      "Postal" : "70471"
    },
    "Business" : {
      "Line1" : "",
      "Line2" : "",
      "State" : "Louisiana",
      "Country" : "USA",
      "City" : "Mandeville",
      "Postal" : "70471"    
    }
  }
};

function onRegSubmit(formData){
  Logger.log("[onRegSubmit] formData: " + JSON.stringify(formData));

  /* validate Data */
  this.openExcelSheet(formData);

  var dataResult = {
    "isSuccess" : true,
    "message" : "Form has been submitted"
  };
  return dataResult;
}


function openExcelSheet(formData) {
  Logger.log("[openExcelSheet] - " + JSON.stringify(formData));
  var data = formData;
  var DealerInfo = this.DealerInfo;  

  DealerInfo.BusinessName = data.businessName;
  DealerInfo.BusinessType = data.businessType;
  DealerInfo.OwnerName = data.ownerName;

  DealerInfo.AccountInfo.Username = data.username;
  DealerInfo.AccountInfo.Email = data.email1;
  DealerInfo.AccountInfo.Password = data.pwd1;

  DealerInfo.Address.Business.Line1 = data.businessLine1;
  DealerInfo.Address.Business.Line2 = data.businessLine2;
  DealerInfo.Address.Business.City = data.businessCity;
  DealerInfo.Address.Business.State = data.businessState;
  DealerInfo.Address.Business.Country = data.businessCountry;
  DealerInfo.Address.Business.Postal = data.businessPostal;

  DealerInfo.Address.Shipping.Line1 = data.shippingLine1 || data.businessLine1;
  DealerInfo.Address.Shipping.Line2 = data.shippingLine2 || data.businessLine2;
  DealerInfo.Address.Shipping.City = data.shippingCity || data.businessCity;
  DealerInfo.Address.Shipping.State = data.shippingState || data.businessState;
  DealerInfo.Address.Shipping.Country = data.shippingCountry || data.businessCountry;
  DealerInfo.Address.Shipping.Postal = data.shippingPostal || data.businessPostal;

  DealerInfo.Address.Billing.Line1 = data.billingLine1 || data.businessLine1;
  DealerInfo.Address.Billing.Line2 = data.billingLine2 || data.businessLine2;
  DealerInfo.Address.Billing.City = data.billingCity || data.businessCity;
  DealerInfo.Address.Billing.State = data.billingState || data.businessState;
  DealerInfo.Address.Billing.Country = data.billingCountry || data.businessCountry;
  DealerInfo.Address.Billing.Postal = data.billingPostal || data.businessPostal;
  Logger.log("[openExcelSheet] - " + JSON.stringify(DealerInfo));


  var ss = SpreadsheetApp.openById('YOURSPREADSHEETID');
  Logger.log("[openExcelSheet] Sheet Opened: '" + ss.getName() + "' (" + ss.getId() + " )");

  var dealerSheet = ss.getSheetByName('YOURSHEETNAME');
  var NowDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyMMddHHmmss")
  var data = [
    NowDate, "",    
    DealerInfo.BusinessName,
    DealerInfo.BusinessType,
    DealerInfo.OwnerNames,
    DealerInfo.AccountInfo.Username,
    "/" + Utilities.base64Encode(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5,  DealerInfo.AccountInfo.Password + "s21")),
    DealerInfo.AccountInfo.Email, "",
    DealerInfo.Address.Shipping.Line1,
    DealerInfo.Address.Shipping.Line2,
    DealerInfo.Address.Shipping.City,
    DealerInfo.Address.Shipping.State,
    DealerInfo.Address.Shipping.Country,
    DealerInfo.Address.Shipping.Postal, "",
    DealerInfo.Address.Billing.Line1,
    DealerInfo.Address.Billing.Line2,
    DealerInfo.Address.Billing.City,
    DealerInfo.Address.Billing.State,
    DealerInfo.Address.Billing.Country,
    DealerInfo.Address.Billing.Postal, "",
    DealerInfo.Address.Business.Line1,
    DealerInfo.Address.Business.Line2,
    DealerInfo.Address.Business.State,
    DealerInfo.Address.Business.Country,
    DealerInfo.Address.Business.City,
    DealerInfo.Address.Business.Postal        
  ];


  dealerSheet.appendRow(data);
  Logger.log("[openExcelSheet] AppendRow (Added Dealer): '" +  JSON.stringify(DealerInfo));

  MailApp.sendEmail("YOUREMAIL@DOMAIN.COM", "Authorized Dealer: New Dealer!", "New Dealer Added.\n " + emailBody() + "\n Sharing https://docs.google.com/spreadsheets/d/" + ss.getId() + "/edit?usp=sharing");
  Logger.log("[openExcelSheet] Emailing YOUREMAIL@DOMAIN.COM");  
}

function hashPassword(pass) {
  return "/" + Utilities.base64Encode(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, pass));
}

function emailBody() {
  var Body = "Business Name: " + DealerInfo.BusinessName + "\n"
    +  "Business Type: " +  DealerInfo.BusinessType + "\n"
     +  "Username: " + DealerInfo.AccountInfo.Username + "\n"
    +  "Password HASH: " + hashPassword(DealerInfo.AccountInfo.Password) + "\n"
    +  "Email: " +  DealerInfo.AccountInfo.Email + "\n"
      +  "Shipping Address \n"
    +  "Line 1: " +  DealerInfo.Address.Shipping.Line1 + "\n"
    +  "Line 2: " +  DealerInfo.Address.Shipping.Line2 + "\n"
    +  "City: " +  DealerInfo.Address.Shipping.City + "\n"
    +  "State: " +  DealerInfo.Address.Shipping.State + "\n"
    +  "Country: " +  DealerInfo.Address.Shipping.Country + "\n"
    +  "Postal: " +  DealerInfo.Address.Shipping.Postal + "\n"
      +  "Billing Address \n"
    +  "Line 1: " +  DealerInfo.Address.Billing.Line1 + "\n"
      +  "Line 2: " +   DealerInfo.Address.Billing.Line2 + "\n"
   +  "City: " +   DealerInfo.Address.Billing.City + "\n"
   +  "State: " +   DealerInfo.Address.Billing.State + "\n"
   +  "Country: " +   DealerInfo.Address.Billing.Country + "\n"
   +  "Postal: " +   DealerInfo.Address.Billing.Postal + "\n"
     +  "Business Address \n"
   +  "Line 1: " +   DealerInfo.Address.Business.Line1 + "\n"     
   +  "Line 2: " + DealerInfo.Address.Business.Line2 + "\n"
    +  "City: " +  DealerInfo.Address.Business.State + "\n"
    +  "State: " +  DealerInfo.Address.Business.Country + "\n"
    +  "Country: " +  DealerInfo.Address.Business.City + "\n"
    +  "Postal: " +  DealerInfo.Address.Business.Postal;

  return Body;
}

function createDocument() {}


function sendMail() {}

registry-form.html

<script>
function onSuccessSubmit(data){
var elm = document.getElementById('result');

elm.innerHTML = "<div>" + data.message + "</div>";
}

function onFailSubmit(data) {

}


</script>


<div id='frmRegisterWrapper'>
<div id="result">Result Holder</div>
<form name="authorizedDealer" id="authorizedDealer" action="register" method="post">

<div class="contact">
<label>Business Name</label><input type="text" name="businessName" placeholder="Business Name" value="" /> <br />
<label>Business Type</label><input type="text" name="businessType" placeholder="Business Type" value="" /> <i>(i.e. Dealer, Broker, Repair Shop, Individual)</i><br />
<label>Owner(s) Name</label><input type="text" name="ownerNames" placeholder="Owner Name" value="" /> <br />
<br />
<label>Point of Contact Name:</label><input type="text" placeholder="Contact Name" value="" /> <br />
<label>Point of Contact Position:</label><input type="text" placeholder="Contact Position" value="" /> <br />
</div>

<fieldset class="address account">
    <legend>Account Information</legend>
    <label>Desired Username</label><input type="text" name="username" placeholder="Username" value="" />
    <br /><br />
    <label>Password</label><input type="password" name="pwd1" placeholder="Password" value="" /> <br />
    <label>Password<br />(Confirm)</label><input type="password" name="pwd2" placeholder="Password" value="" />
    <br /><br />
    <label>Email Address</label><input type="text" name="email1" placeholder="Email Address" value="" /> <br />
    <label>Email Address<br/>(Confirm)</label><input type="text" name="email2" placeholder="Email Address" value="" /> <br />   
    <p>
        Username & Password:
        <ul>
            <li>Cannot be longer than 16 characters</li> 
            <li>Must be alphanumeric</li>
            <li>Must contain at least 1 numeric</li>
        </ul>
    </p>
    <hr />  

<fieldset class="address business">
<legend>Business Address</legend>
<label>Address Line 1:</label><input type="text" name="businessLine1" placeholder="Address Line 1" value="" /> <br />
<label>Address Line 2:</label><input type="text" name="businessLine2" placeholder="Address Line 2" value="" /> <br />
<label>City:</label><input type="text" name="businessCity" placeholder="City" value="" /> <br />
<label>State:</label><input type="text" name="businessState" placeholder="State" value="" /> <br />
<label>Country:</label><input type="text" name="businessCountry" placeholder="Country" value="" /> <br />
<label>Postal:</label><input type="text" name="businessPostal" placeholder="Postal" value="" /> <br />
</fieldset>

<fieldset class="address billing">
<legend>Billing Address</legend>

<label>Address Line 1:</label><input type="text" name="billingLine1" placeholder="Address Line 1" value="" /> <br />
<label>Address Line 2:</label><input type="text" name="billingLine2" placeholder="Address Line 2" value="" /> <br />
<label>City:</label><input type="text" name="billingCity" placeholder="City" value="" /> <br />
<label>State:</label><input type="text" name="billingState" placeholder="State" value="" /> <br />
<label>Country:</label><input type="text" name="billingCountry" placeholder="Country" value="" /> <br />
<label>Postal:</label><input type="text" name="billingPostal" placeholder="Postal" value="" /> <br />

</fieldset>

<fieldset class="address shipping">
<legend>Shipping Address</legend>

<label>Address Line 1:</label><input type="text" name="shippingLine1" placeholder="Address Line 1" value="" /> <br />
<label>Address Line 2:</label><input type="text" name="shippingLine2" placeholder="Address Line 2" value="" /> <br />
<label>City:</label><input type="text" name="shippingCity" placeholder="City" value="" /> <br />
<label>State:</label><input type="text" name="shippingState" placeholder="State" value="" /> <br />
<label>Country:</label><input type="text" name="shippingCountry" placeholder="Country" value="" /> <br />
<label>Postal:</label><input type="text" name="shippingPostal" placeholder="Postal" value="" /> <br />
</fieldset>
</fieldset>
<button onclick="google.script.run.withSuccessHandler(onSuccessSubmit).onRegSubmit(this.parentNode)">Submit</button>
</form>
</div>







<style>
div#frmRegisterWrapper {font-size:16px;}
div#frmRegisterWrapper fieldset.address.account {}
div#frmRegisterWrapper fieldset.address.account label {width:200px;font-size:12px;}
div#frmRegisterWrapper fieldset.address.account input[type="text"] {}
div#frmRegisterWrapper fieldset.address.account p {background-color: #ECE63D;font-weight:700;padding:1em 2em;border:0.09em solid silver;}

div#frmRegisterWrapper fieldset.address { margin:1em auto;padding:3em;display:block;width:600px;}
div#frmRegisterWrapper fieldset.address label {
min-width: 190px;
color: #404040;
display: inline-block;
padding: 0.5em;
text-align: right;
text-shadow: 0em 0em 0.1em rgb(177, 177, 177);
/* box-shadow: inset 0em -1em 3.5em 0em rgb(0, 129, 36); */
font-size: 1.05em;
font-weight: 700;
letter-spacing: 0.05em;
margin-top: 0.1em;
font-family: Verdana;
}

form > button {padding:1em;background-color:green;color:white;text-shadow:0em 0em 0.3em black;}

div#frmRegisterWrapper fieldset.address.shipping {}
div#frmRegisterWrapper fieldset.address.billing {}
div#frmRegisterWrapper fieldset.address.business {}

div#frmRegisterWrapper fieldset.address input {
    line-height: 1.5em;
    height: 1.5em;
    font-size: 1.25em;
    box-shadow: 0.1em 0.1em 0.25em -0.05em black;
    border: 0.1em solid whitesmoke;
    outline: 0;
    padding-left: 0.5em;
    border: 0.05em solid silver;
}

div#frmRegisterWrapper legend {
    font-family: 'Calibri';
    font-size: 2.1em;
    font-weight: 700;
    text-shadow: 0.05em 0.05em 0.05em gray;
}

div#frmRegisterWrapper fieldset {border:0.09em solid silver;border-radius:0.5em;padding:2em auto;}
div#frmRegisterWrapper div.contact {margin:0em auto;padding:1em;background-color:whitesmoke;margin-bottom:3em;border-bottom:0.1em solid silver;}

div#frmRegisterWrapper div.contact label {display:inline-block;width:200px;}

div#frmRegisterWrapper div.contact input {line-height:1.2em;height:1.2em;}
</style>
  • At the point you have your App Script project up, you should review Google App Script tutorials. – Brett Caswell Nov 7 '14 at 16:59
  • Deploy the app as a web service, ensure you name your project and save your version (it will make it 1 the first time you do this).. You'll be prompted to run as either yourself or the user who is accessing the app.. Oauth is built into these apps, so visitors will be prompted with a permission access request, the permission access depends on the behavior of your app.. it's instrict.. – Brett Caswell Nov 7 '14 at 17:05
0

The formula in B2:

=if(A2="a",A3,A4)  

will return the content of A3 (in the example, ValueA3) if A2 contains a (and nothing else) and otherwise will return the content of A4:

WA69738 example

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.