I know this topic has bee covered before sort of, but none of it really makes sense to me. Basically I wrote a function that will generate a PDF from some API data using user inputs. It looks vaguely like this and works when I run it in the script editor.

function myfunction(InputA,InputB,InputC,InputD,InputE) {

......

var sourceSpreadsheet = SpreadsheetApp.getActive();
    var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
    if (parents.hasNext()) {
      var folder = parents.next();
    }

    var response = UrlFetchApp.fetch(url, options)
    var blob = response.getAs('application/pdf').setName(InputA   InputB   InputC)
    var newFile = folder.createFile(blob);

    return newFile
}
  

The problem is Google permissions. The classic: "You don't have permission to access the drive".

I have tried publishing the script as a private sheets addon and enabled it on my spreadsheet. But that didn't really do anything. And I don't really know why because I authorized the app for all the required scopes when I approved the add on. I can see it in the extensions menu but I am still getting errors when I try to call the function.

The button method of enabling permissions doesn't work for me because I need to run the code several times based on parameters defined in the Sheet. I tried simple triggers since I want the code to run weekly anyways, but found the same problem.

Can someone give me the step by step of how I'm supposed to do this.

Please don't send links to the google documentation because I have read the related pages and still don't know what I'm doing wrong.

CodePudding user response：

I recommend you use an installable onEdit trigger. I have this approach and see if it works for you.

Sample Data:

• Assuming url is from Input D column.
• Create a column of checkboxes that will trigger the installed trigger. In my case, ticking it will create the file and unticking it will remove the file created.
• Ticking rows where an input (at least 1) is missing, will cancel the creation of the file and then untick the checkbox ticked.

Drive folder:

Script:

function createFileTrigger(e) {
  var spreadsheet = e.source;
  var sheet = spreadsheet.getActiveSheet();
  var range = e.range;
  var value = e.value;
  var row = range.getRow();
  var col = range.getColumn();

  // proceed if edited cell is Sheet1!H2:H
  if(sheet.getSheetName() == 'Sheet1' && col == 8 && row > 1) {
    // if checkbox is ticked
    if(value == 'TRUE') {
      // inputs = [Input A, Input B, Input C, Input D, Input E]
      var inputs = range.offset(0, -6, 1, 5).getValues().flat();

      var parents = DriveApp.getFileById(spreadsheet.getId()).getParents();
      var folder = parents.next();

      // set some conditions here to pre-check the inputs
      // e.g. if at least 1 input is blank, cancel file creation (return)
      if(inputs.filter(String).length < 5) {
        // untick the checkbox ticked
        range.setValue('FALSE');
        // skip creation of file
        return;
      }

      // assuming url is from Input D (removed options as not needed for presentation)
      var response = UrlFetchApp.fetch(inputs[3]);
      var newFileName = `${inputs[0]} ${inputs[1]} ${inputs[2]}`;

      // if file is existing (which should not happen but just in case)
      if(folder.getFilesByName(newFileName).hasNext()) {
        // do something else that is needed to be done to avoid duplication of file
        // e.g. overwrite or skip creating file
        console.log(newFileName   ' is already existing in the parent folder'); 
      }
      // if not existing
      else {
        // create the file
        var blob = response.getAs('application/pdf').setName(newFileName)
        // for presenation purposes, will write the id of the created file
        range.offset(0, -1).setValue(folder.createFile(blob).getId());
      }
    }
    // if checkbox is unticked
    else {
      // do something else that is needed to be done
      // e.g. delete the file using the id returned (using Drive Advanced Services)
      var fileIdRange = range.offset(0, -1);
      Drive.Files.remove(fileIdRange.getValue());
      // remove file id content on the cell
      fileIdRange.clearContent();
    }
  }
}

Ticking checkbox (folder):

Ticking checkbox (sheet):

Note:

• This can still be improved, but should already be enough for your case.