Google AppsScript

前端操作 Apps Script 上傳檔案到 Google Drive 並取得連結﹍實作範例
Upload file to my google drive with Google Apps Script (NO FORM IN GOOGLE)

Table of Contents

• Google AppsScript
  • Table of Contents
  • Google Drive Upload
    • Google Apps Script
    • Javascript
  • Google Drive Upload and MySQL insert
    • Google Apps Script
    • Google Apps Script Debugger
    • Javascript
    • 無法存取或檔案遺失錯誤

Google Drive Upload

Google Apps Script

1. Create new project of Google Apps Script.
   Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.
   If you want to directly create it, please access to https://script.new/. In this case, if you are not logged in Google, the log in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

2. Prepare script.
   Please copy and paste the following script (Google Apps Script) to the script editor. This script is for the Web Apps.

   Server side: Google Apps Script
   Please set the folder ID that you want to put the file.

var folderId = "Your_ID",
folder = DriveApp.getFolderById(folderId);

function doPost(e) {
  var para = e.parameter,
      userID = para.userID,
      Isfirst = para.Isfirst,
      fileName = para.fileName,
      fileType = para.fileType,
      base64Data = para.base64Data;
  
  var returnurl = getFileUrl(fileName, fileType, base64Data, userID, Isfirst);
  return returnurl;

}

function getFileUrl(fileName, fileType, base64Data, userID, Isfirst) {
  var data = Utilities.base64Decode(base64Data),
      blob = Utilities.newBlob(data, fileType, fileName),
      file = folder.createFile(blob),
      fileUrl = file.getUrl();
  testMySQL(userID, fileUrl, Isfirst);
  return ContentService.createTextOutput(fileUrl);
}

function testMySQL(userID, fileUrl, Isfirst){
  var conn = Jdbc.getConnection('jdbc:mysql://IP:Port/DBname', 'usrname', 'pwd');
 
  var stmt = conn.createStatement();
  var start = new Date();
  //var sql = "select * from `Gait_pattern_Video_path`";
  if(Isfirst==1){
    var sql = "INSERT INTO Gait_pattern_Video_path (`ID`, `Video path`, `viewed`) VALUES ('" + userID + "', '" + fileUrl + "', -1)";
  } else{
    var sql = "INSERT INTO Gait_pattern_Video_path (`ID`, `Video path`, `viewed`) VALUES ('" + userID + "', '" + fileUrl + "', 0)";
  }
  
  Logger.log('SQL:' + sql);
  var rs = stmt.executeUpdate(sql);
}

3. Deploy Web Apps.

   1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".

   2. Select "Me" for "Execute the app as:".

      • By this, the script is run as the owner.

   3. Select "Anyone, even anonymous" for "Who has access to the app:".

   4. Click "Deploy" button as new "Project version".

   5. Automatically open a dialog box of "Authorization required".

      1. Click "Review Permissions".
      2. Select own account.
      3. Click "Advanced" at "This app isn't verified".
      4. Click "Go to ### project name ###(unsafe)"
      5. Click "Allow" button.

   6. Click "OK".

   7. Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.

      • When you modified the Google Apps Script, please redeploy as new version.(以新版本發布，才會更新) By this, the modified script is reflected to Web Apps. Please be careful this.

Javascript

//The file you want to upload
<input type="file" id="input" value="">
<div id="fileUrl"></div>

<script src='//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js'></script>
<script>
(function($) {
//The webapp url, which is created in Apps Script.
var appUrl = "https://script.google.com/macros/s/xxxxxxxxxxxxxxxxxx/exec", file, fileReader;
$("#input").change(function() {
file = this.files[0];
if (file) {
fileReader = new FileReader();
fileReader.onload = getFileInfo;
fileReader.readAsDataURL(file);
}
});
function getFileInfo(evt) {
var fileName = file.name,
fileType = file.type,
dataUrl = evt.target.result,
base64Data = dataUrl.split(",")[1];
uploadFile(fileName, fileType, base64Data);
}
function uploadFile(fileName, fileType, base64Data) {
$.ajax({
type: "post",
data: {
"fileName": fileName,
"fileType": fileType,
"base64Data": base64Data
},
url: appUrl,
success: function(fileUrl) { // 成功時回傳檔案網址
$("#fileUrl").html(fileUrl);
},
error: function(e) {
console.log(JSON.stringify(e));
}
});
}
})(jQuery);
</script>

Google Drive Upload and MySQL insert

Google Apps Script

var folderId = "YOUR_DIRID",
folder = DriveApp.getFolderById(folderId);

function doPost(e) {
  // Prepare the file and related info
  var para = e.parameter,
      userID = para.userID,
      fileName = para.fileName,
      fileType = para.fileType,
      base64Data = para.base64Data;
  // Call getFileUrl to upload file
  getFileUrl(fileName, fileType, base64Data, userID);
  return ContentService.createTextOutput("success");

}

function getFileUrl(fileName, fileType, base64Data, userID) {
  // Upload file 
  var data = Utilities.base64Decode(base64Data),
      blob = Utilities.newBlob(data, fileType, fileName),
      file = folder.createFile(blob),
      fileUrl = file.getUrl();// Get the url of uploaded file
  // Call testMySQL to insert your data into DB (here I want to insert `userID` and `fileUrl`)
  testMySQL(userID, fileUrl);
}

function testMySQL(userID, fileUrl){
  // Set the jdbc connection
  var conn = Jdbc.getConnection('jdbc:mysql://YOURIP:YOUR_PORT/YOUR_DBNAME', 'YOUR_USRNAME', 'YOUR_PWD');
 
  var stmt = conn.createStatement();
  var start = new Date();
  var sql = "INSERT INTO Gait_pattern_Video_path (`ID`, `Video path`) VALUES ('" + userID + "', '" + fileUrl + "')";
  Logger.log('SQL:' + sql);
  // **Important** If you want to query a table, use `executeQuery`. 
  // Otherwise, use `executeUpdate`
  var rs = stmt.executeUpdate(sql);
}

Google Apps Script Debugger

• 內建的 Debugger 可以用來看目前寫的 function 是否有一些語法上的錯誤
  
  

Javascript

<script src='//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js'></script>
<script>
(function($) {
    $("#chosen").change(function(){
        var $source = $('#video_here');
        $source[0].src = URL.createObjectURL(this.files[0]);
        $source.parent()[0].load();
    });

    $("#player").error(function(e){
        switch (e.target.error.code) {
         case e.target.error.MEDIA_ERR_ABORTED:
           alert('You aborted the video playback.');
           break;
         case e.target.error.MEDIA_ERR_NETWORK:
           alert('A network error caused the video download to fail part-way.');
           break;
         case e.target.error.MEDIA_ERR_DECODE:
           alert('The video playback was aborted due to a corruption problem or because the video used features your browser did not support.');
           break;
         case e.target.error.MEDIA_ERR_SRC_NOT_SUPPORTED:
           alert('The video could not be loaded, either because the server or network failed or because the format is not supported.');
           break;
         default:
           alert('An unknown error occurred.');
           break;
       }
    });


    var appUrl = "YOUR_DIRID", file, fileReader;// 網路應用程式網址 PT的

    $("#uploadvideo").click(function() {

        file = $('#chosen').prop('files')[0];
        if (file) {
            $("#fileUrl").html("Uploading...");
            var fileReader = new FileReader();
            fileReader.onload = getFileInfo;
            fileReader.readAsDataURL(file);
        }
        else {
            $("#fileUrl").html("Select a file!");
        }
    });
    function getFileInfo(evt) {
        var fileName = file.name,
        fileType = file.type,
        dataUrl = evt.target.result,
        base64Data = dataUrl.split(",")[1];
        uploadFile(fileName, fileType, base64Data);
    }
    // The data you want to post to appsscript based web 
    function uploadFile(fileName, fileType, base64Data) {
        $.ajax({
            type: "post",
            data: {
            "userID": "<%=usrID%>",
            "fileName": fileName,
            "fileType": fileType,
            "base64Data": base64Data
            },
            url: appUrl,
            success: function(fileUrl) { 
                alert('Upload Successful');
                $("#fileUrl").html(fileUrl);// 成功時回傳檔案網址
                //$("#fileUrl").html("Sucessfully Upload");
            },
            error: function(e) {
                alert('Upload Failed');
                $("#fileUrl").html(JSON.stringify(e));
            }
        });
    }

})(jQuery);

無法存取或檔案遺失錯誤

Sol: 開無痕式視窗，重新登入就可以開啟