Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This is a possible way to get the result you want, I wrote it as a custom function, ie you have to put it in the the cell where you want it to appear in the form <code>=dayToToday()</code></p> <p>here is the script with a few logs and comments to show intermediate values.</p> <pre><code>function dayToToday(x){ var sh = SpreadsheetApp.getActiveSheet(); Logger.log(sh.getActiveCell().getRowIndex()) var refcell = sh.getRange(sh.getActiveCell().getRowIndex(),1).getValue();;// get value in column A to get the reference date var refTime = new Date(refcell); var ref = refTime.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days var today = new Date(); var TD = today.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days var day = parseInt(TD-ref);// get the difference in days (integer value ) return day ; // return result that will be in cell } </code></pre> <hr> <p><strong>EDIT :</strong> if I understand your use case I suggest you abandon the custom function aproach <em>(that I never use since I don't like it so much)</em> and go for this solution that uses a timer trigger and/or a menu to update values in your spreadsheet in one batch.</p> <p>(ps : sorry for not having answer quickly enough... too busy these days :-)</p> <pre><code>function onOpen() { var menuEntries = [ {name: "test function", functionName: "toTrigger"}, ]; var ss = SpreadsheetApp.getActiveSpreadsheet(); ss.addMenu("test menu",menuEntries);// } // create a timer trigger that will call "toTrigger" every 15 minutes function toTrigger(){ var sh = SpreadsheetApp.getActiveSheet(); var data = sh.getRange(1,1,sh.getLastRow(),2).getValues(); for(var n=0;n&lt;data.length;++n){ if(typeof(data[n][0])=='object'){ data[n][1]=dayToToday(data[n][0]) } } sh.getRange(1,1,sh.getLastRow(),2).setValues(data) } function dayToToday(x){ var refcell = x;;// get value in column A to get the reference date var refTime = new Date(refcell); var ref = refTime.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days var today = new Date(); var TD = today.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days var day = parseInt(TD-ref);// get the difference in days (integer value ) return day ; // return result that will be in cell } </code></pre> <hr> <p><strong>second EDIT</strong>:</p> <p>when using <code>var data = sh.getRange(1,1,sh.getLastRow(),2).getValues();</code></p> <p>the numbers in getRange are start row, start column, number of Rows, number of Columns so you'll have to gat a range that includes the columns you want and then use data [n][columnNumber-1] to get the corresponding value. (-1 because array start from 0 and columns count from 1 (A in fact but A is first column ;-)) if you need a variable reference then let me know because the function must be modified to accept 2 variables (a ref and another one).</p> <hr> <p><strong>LAST EDIT</strong> (I hope)</p> <p>I changed some details... see [9] and [5] index corresponding to J and F</p> <pre><code>function toTrigger(){ var sh = SpreadsheetApp.getActiveSheet(); var data = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getValues(); for(var n=0;n&lt;data.length;++n){ if(typeof(data[n][9])=='object'){ data[n][5]=dayToToday(data[n][9]) } } sh.getRange(1,1,data.length,data[0].length).setValues(data) } </code></pre> <hr> <p><strong>update/edit</strong></p> <p>result in hours (following request in comments)</p> <pre><code>function hoursToToday(x){ var refcell = x;;// get value in column A to get the reference date var refTime = new Date(refcell); var refhrs = refTime.getHours(); var ref = refTime.setHours(refhrs,0,0,0)/3600000;// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days var today = new Date(); var todayHrs = today.getHours() var TD = today.setHours(todayHrs,0,0,0)/3600000;// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days var hrs = TD-ref;// get the difference in days (integer value ) return hrs ; // return result that will be in cell } </code></pre> <hr>
    singulars
    1. This table or related slice is empty.
    1. POInsert the result count number of days between two days in spreadsheet using google script
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USSerge insas
    1. USSerge insas
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POInsert the result count number of days between two days in spreadsheet using google script
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COHi Serge, regarding your Script. The value of days I needs to insert in the column 2. The sheet have around 50 rows and other more. The script should insert the result on the column 2 for each row. I mean if I have 50row the script should be execute for every row. For the moment I can not insert the result in the column. I don't know why is not working.
      singulars
      1. PO
      1. USLAD Service Desk
    2. COhave a look at this [ss for example](https://docs.google.com/spreadsheet/ccc?key=0AnqSFd3iikE3dFRDb2NCX3NqMmdmYU5TclNsSXZOUWc&usp=sharing) (make a copy to be able to edit it)
      singulars
      1. PO
      1. USSerge insas
    3. COIf possible the script insert directly the solution into the cell without write any funtion in the colummn? as per my last comment
      singulars
      1. PO
      1. USLAD Service Desk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload