Date Calculations




Date Calculations:

Contents

  1. 1 Date Calculations from Last Cell: 
    1. 1.1 Table Gadget (1min interval set):
    2. 1.2 Direct Insert Spreadsheet:
  2. 2 Insert a Spreadsheet Cell Range:
  3. 3 Publish Sheet2 only - of the Spreadsheet:
  4. 4 Google Gadget Properties:
  5. 5 Insert > More Gadgets > Add Gadget: http://www.google.com/ig/modules/table.xml
  6. 6 Last Cell Functions:
    1. 6.1 More Advanced Functions:
  7. 7 Apps Script:
  8. 8 Apps Script Add to Calendar:


Date Calculations - For Example: 
On your Business website you may want to have a Home Rental booking system. Saving time and money processing daily inquiries.

So the Ideal system would be...

Booking Form 
Submit Dates (Normal Form Submit as "Get Rates" for the Calculation Spreadsheet PUBLIC) 
Preview Rates 
Submit Form and Payment (Main Booking Form - PRIVATE).

For the moment it will be just -  Submit Dates (to Calculation Spreadsheet) - Date Calculations.



Submit 2 Dates for Calculation


Refresh Page and Date Calculations.

"Guest House" - Form and Google Calendar

Date Calculations from Last Cell: 

(Last Cells of Form Submission)


Refresh Page to Refresh Calculations 
(Refresh interval is set for 1 min)

Table Gadget (1min interval set):

The gadget spec URL could not be found


Direct Insert Spreadsheet:

+ Calculation Diagram/Chart (Not visible in the Table Gadget)

(No Interval - Full spreadsheet visible to logged in Owners/Editors/Viewers)

Only Sheet2 Published ‎‎(Sheet1 Private)‎‎- Date Calculations

Insert a Spreadsheet Cell Range:

To insert a Spreadsheet cell range in Google Sites. Use The Standard Google 
Gadget
 [ iGoogle Modules ]  - 

http://www.google.com/ig/modules/table.xml

Publish Sheet2 only - of the Spreadsheet:



Published Spreadsheet Cell Range:
Share > Publish to the Web (
Settings:
Automatically  Republish)> Get a link to the published data (Settings: Webpage/Sheet2/A1:C20) > Copy the URL link generated:

https://spreadsheets.google.com/pub?key=0AjU_rJcQHTNFdDg0bV9kb3lyZHl1eV9zaUEyMTlvVEE&hl=en&single=true&gid=1&range=A1%3AC20




Google Gadget Properties:


Insert > More Gadgets > Add Gadget: http://www.google.com/ig/modules/table.xml


Add your Published Spreadsheet URL to the Table Google Gadget. 

Adjust the Google Gadget Properties as you like. (eg. remove sorting, interval etc...)

Last Cell Functions:


To get the Last Cell from a Form Submission.

You can place the following functions in a new Sheet ("+" button [bottom-right]) of the forms spreadsheet :

Add the following functions to any cell in sheet2:

Arrival Date (Sheet1 Column C) =index(sheet1!C:C,MAX(IF(sheet1!C:C<>"",ROW(sheet1!C:C))),1)

Departure Date (Sheet1 Column E) =index(Sheet1!E:E,MAX(IF(Sheet1!E:E<>"",ROW(Sheet1!E:E))),1)

You will need to adjust the columns in the functions (column A = A:A etc..).

More Advanced Functions:



Apps Script:


NEW Google Apps Scripts is now integrated into Google Sites. Google Apps Scripts allows you to access other Google accounts  and create entities just by using code through Google spreadsheets. 

This will make Google spreadsheets very powerful in developing everyday Business solutions. More Examples and Google Sites Tutorials are Being developed , while some services still remain in Labs/Experimental.There is great potential for power calculators and compute engines. The savings of these normally expensive products in the cloud is massive.

Any Specific Google Apps Scripts Example can be suggested through Google Moderator HERE.


Apps Script Add to Calendar:


Any submissions using the Form can be added to the calendar. The Best Practice for this type of System would be to authorize and confirm the Booking first, before adding to the Calendar.

For the moment we will add any form submissions data to the calendar using a simple Apps Script.


Extending the 'Date Calculations' System with a Calendar Example using Apps Script


More Apps Script Examples:



Guest House Demo