Google-sheets – Google Sheets to SQL type database

google sheetsmigrate-data

Would it be possible to automatically export a Google sheet to a SQL type database which I could query? I am trying to make a information dashboard out of the sheet that contains data from a Google form. What possible ways could I do this?

Best Answer

If you're ok with the data being public (file>publish as html), you can expose/publish Google sheets as CSV/TSV/etc, then you could automate an import process with that URL as the source.

You can also access the data as JSON which might make the database import unnecessary (assuming the data is on the first sheet) https://spreadsheets.google.com/feeds/list/YOURDOCID/1/public/values?alt=json

Code

function mySQL(ref, dates) {
  var output = [];
  for(var i = 0, iLen = ref.length; i < iLen; i++) {
    var user = ref[i][0], begin = new Date(ref[i][1]), end = new Date(ref[i][2]);
    for(var j = 0, jLen = dates.length; j < jLen; j++) {
      var date = new Date(dates[j]);
      if(date >= begin && date <= end) {
        output.push([date, user]);
      }
    }
  }
  return output;  
}

Screenshot

data
enter image description here

outcome
enter image description here

Example

I've added the script to your example file.

Google Sheets – Forms Data Manipulation

Short answer

Add a new sheet.
Add the following formulas to the new sheet

Cell A2 -> Headers

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,0)

This could be repeated several times, one for each header column, just change the last parameter accordingly.

Cell B2 -> data

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*2+1,1,2)

Fill down as necessary

Explanation

OFFSET() is used to get the values from the source data.
'Form responses 1'!$A$2 is the start point to count columns and rows.
row() is used to increment the row and column iterators.
ceiling((row()-1)/5,1), row iterator, is used to set the row from the source data.
mod(row()-2,5)*2+1, column iterator, is used to set the column from the source data.
In the data formula the last parameter, indicates the number of data columns.

In case that are several "header data" columns, add 1 for each of them. I.E. if there are five "header data" columns, and six "data columns" the column iterator should be

mod(row()-2,5)*6+5

The final data formula to be added to the F2 cell is:

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*6+5,1,6)

In the row and column iterators, 5 is used because there are 5 columns for "record data".

Demo

Spreadsheet