Export to Google sheet

Export to Google sheet

pmconsultingpmconsulting Posts: 36Questions: 11Answers: 0

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.

Replies

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • hAtul89hAtul89 Posts: 12Questions: 7Answers: 0

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

  • nkcnkc Posts: 1Questions: 0Answers: 0

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

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    Hi all,

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

    Cheers,

    Colin

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • tanguypamtanguypam Posts: 5Questions: 2Answers: 0

    Hi guys :smile: 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";
    }
    
    ?>
    

    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. :smile:

    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 :smiley:

    Thank you very much,

    Tanguy

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

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

    Colin

  • tanguypamtanguypam Posts: 5Questions: 2Answers: 0

    Thanks Colin,

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

    window.open(data, '_blank');

This discussion has been closed.