Question

0

370

Visualizações

Upload PDF file in Google sheet using Google apps script

I am trying to upload pdf file to a single cell in google sheet just like we insert image in a google sheet cell. I've searched for quite some time now, but haven't been able to find any solutions to this. I have tried the following code:

function onOpen(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var menuEntries = [];
  menuEntries.push({name: "File...", functionName: "doGet"});
  ss.addMenu("Attach ...", menuEntries);
}

function doGet(e) {
  var app = UiApp.createApplication().setTitle("upload attachment into Google Drive");
  SpreadsheetApp.getActiveSpreadsheet().show(app);
  var form = app.createFormPanel().setId('frm').setEncoding('multipart/form-data');
  var formContent = app.createVerticalPanel();
  form.add(formContent);  
  formContent.add(app.createFileUpload().setName('thefile'));

  formContent.add(app.createHidden("activeCell", SpreadsheetApp.getActiveRange().getA1Notation()));
  formContent.add(app.createHidden("activeSheet", SpreadsheetApp.getActiveSheet().getName()));
  formContent.add(app.createHidden("activeSpreadsheet", SpreadsheetApp.getActiveSpreadsheet().getId()));
  formContent.add(app.createSubmitButton('Submit'));
  app.add(form);
  SpreadsheetApp.getActiveSpreadsheet().show(app);
  return app;
}

function doPost(e) {
  var app = UiApp.getActiveApplication();
  app.createLabel('saving...');
  var fileBlob = e.parameter.thefile;
  var doc = DriveApp.getFolderById('0B0uw1JCogWHuc29FWFJMWmc3Z1k').createFile(fileBlob);
  var label = app.createLabel('file uploaded successfully');


  var value = 'hyperlink("' + doc.getUrl() + '";"' + doc.getName() + '")'
  var activeSpreadsheet = e.parameter.activeSpreadsheet;
  var activeSheet = e.parameter.activeSheet;
  var activeCell = e.parameter.activeCell;
  var label = app.createLabel('file uploaded successfully');
  app.add(label);
  SpreadsheetApp.openById(activeSpreadsheet).getSheetByName(activeSheet).getRange(activeCell).setFormula(value);
  app.close();
  return app;
}

Since UiApp has been deprecated so it shows the error "UiApp has been deprecated. Please use HtmlService instead". I have tried the following line to avoid UiApp error but of no use:

var app = HtmlService.createHtmlOutput();

Is there any workaround that we can get to avoid this error? Thank you.

about 4 years ago · Santiago Trujillo

1 Respostas

Responde à pergunta

0

Responde à pergunta

Encontrar trabalhos remotos

Accepted Answer · 2022-07-06T14:47:36.815Z

I believe your goal is as follows.

From your following comments,

I have pdf file in local pc, I would have a google sheet add-on with an option "file upload". I would click on that, it will upload file in the Drive folder which we have specified in script and the active google sheet cell will show the confirmation message.

I want to put the message in currently active cell of the sheet
You wanted to upload a PDF file on the local PC to Google Drive. And you want to put the confirmation message to a current active cell. You want to achieve this by a sidebar using HTML and Google Apps Script.

In this case, how about the following sample script?

Sample script:

Google Apps Script side: `Code.gs`

Please copy and paste the following script to the script editor of Google Spreadsheet as the script file and save the script.

const openSidebar = _ => SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("index"));

function upload(e){
  const message = "sample confirmation message"; // Please set your confirmation message.

  DriveApp.createFile(Utilities.newBlob(...e));
  SpreadsheetApp.getActiveRange().setValue(message);
}

HTML&Javascript side: `index.html`

Please copy and paste the following script to the script editor of Google Spreadsheet as the HTML file and save the script.

<form>
  <input type="file" name="file" onchange="upload(this.parentNode)" accept=".pdf,application/pdf" />
</form>
<script>
function upload(e){
  const file = e.file.files[0];
  const fr = new FileReader();
  fr.onload = e => google.script.run.upload([[...new Int8Array(e.target.result)], file.type, file.name]);
  fr.readAsArrayBuffer(file);
}
</script>

Testing:

When you run the function openSidebar, the sidebar is opened. And when you select the file from the input tag, the file is uploaded to the root folder of Google Drive, and the confirmation message is put to the active cell.

Note:

In this method, the maximum file size is 50 MB because of the specification of the Google Apps Script side. Please be careful about this.
This is a simple sample script for achieving the goal of your question. So please modify this for your actual situation.

Reference:

Dialogs and Sidebars in Google Workspace Documents

0

370