Export to Google sheet

pmconsulting

There must be a way to get the CSV or EXCEL export buttons to open the download in a Google sheet on an iPad. I hope that’s just something I’m missing. Would be really bad news if this couldn’t be arranged. If Apple blocks that, we need a website and letter writing campaign to ask Tim Cook to fix that.

allan

I'm certain there will be a way to do it - but its not something I've investigated yet I'm afraid. There is a Google Sheets API which includes the ability to create sheets, so I think that would be the way to go about it.

Allan

hAtul89

Did you find a solution to this? I'm trying to convert to google sheets too.

nkc

agreed - export to google sheet function would be super useful.

colin

Hi all,

Sorry, there's been no movement on this, and unlikely to be in the near-future due to other priorities.

Cheers,

Colin

allan

This is an area we'd very much welcome community input. You can get the data from DataTables using our API methods and then import it into Google Sheets using the API I linked to above. If anyone does this, please post it here and we'll flag it up for others to also be able to use.

Allan

tanguypam

Hi guys Good news !

I succeeded to make an export button to google sheets via the GDrive API and the GSheets API. Those APIs have to be activated on your Google account, if you don't know how to do it, just take a look at the Google Help.
Drive : https://developers.google.com/drive/api/v3/quickstart/php
Sheets : https://developers.google.com/sheets/api/quickstart/php

So, first, you have to create a new button in your Datatable.

buttons: [
      {
        text: 'Export to GSheets',
        action: function ( e, dt, button, config ) {
                    var data = dt.buttons.exportData();
                    exportToSheets(data);
                  }
      }
]

I associated a function to it :

function exportToSheets(data){
  $.ajax({
    type: "POST",
    url: "ajax/exporttosheets.php",
    data:{data:data},
    success:function(data){
      console.log(data); 
    }
  })
}

And my exporttosheets.php look like that :

```
<?php

if(!empty($_POST['data'])){
$data=$_POST['data'];
$header=$data["header"];
$body=$data["body"];
$footer=$data["footer"];
// var_dump($body);
include('../vendor/autoload.php');
//Create GSheets
$client = new \Google_Client();
$client->setApplicationName('Create Sheet');
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
$client->setAuthConfig('../credentials.json');
$service = new Google_Service_Sheets($client);
$spreadsheet = new Google_Service_Sheets_Spreadsheet([
'properties' => [
'title' => 'New Spreadsheet'
]
]);
$spreadsheet = $service->spreadsheets->create($spreadsheet, [
'fields' => 'spreadsheetId'
]);
$fileId=$spreadsheet->spreadsheetId;
// echo $fileId;

//Give permissions
$client2 = new \Google_Client();
$client2->setApplicationName('Give permissions');
$client2->setScopes([\Google_Service_Drive::DRIVE]);
$client2->setAccessType('offline');
$client2->setAuthConfig('../credentials.json');
$client2->setPrompt('select_account consent');
$service2 = new Google_Service_Drive($client2);
$newPermission = new Google_Service_Drive_Permission();
$newPermission->setEmailAddress("user@example.com");
$newPermission->setType('user');
$newPermission->setRole('writer');
$fileId=$spreadsheet->spreadsheetId;
$service2->permissions->create($fileId, $newPermission);
// echo "https://docs.google.com/spreadsheets/d/$fileId";

//Add data from the dataTables
$client3 = new \Google_Client();
$client3->setApplicationName('Add data from datatables');
$client3->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client3->setAccessType('offline');
$client3->setAuthConfig('../credentials.json');
$service3 = new Google_Service_Sheets($client3);
$spreadsheetId = $fileId;
$range = 'Sheet1';
$values=$body;
$body = new Google_Service_Sheets_ValueRange([
'values' => $values
]);
$params = [
'valueInputOption' => 'RAW'
];
$insert = [
"insertDataOption" => "INSERT_ROWS"
];
$result = $service3->spreadsheets_values->append(
$spreadsheetId,
$range,
$body,
$params,
$insert
);
echo "https://docs.google.com/spreadsheets/d/$fileId";
}

<?php > ``` ?>

As you see, i return the complete URL of the sheet, and in the jQuery function and I open a new tab to this Sheets.

I have one issue here, I don't know how to add the header to the body. Here I only get the data ($body) w/o the names of the columns at the top of the GSheets. If you can help me that would be great

Thank you very much,

Tanguy

colin

Nice, thanks for sharing. I don't know the answer regarding the header, but hopefully somebody else in the community does.

Colin

tanguypam

Thanks Colin,

Oh, I forgot to change the console.log(data) , so, to open in a new tab :

window.open(data, '_blank');