CODEWITHSUNDEEP
google-apps-scriptgoogle-drive-apigoogle-oauth
Rafał Pydyniak
Rafał Pydyniak

Reputation: 539

Custom google app script doesn't work after copying spreadsheet with google java client

I have google spreadsheet template with custom add-on which I'm trying to copy

def copyTemplateSpreadsheet(Drive driveService) {
    File templateCopy = new File()
    templateCopy.setName("excel-template")

    def copiedFile = driveService.files().copy(templateSpreadsheetId, templateCopy).execute()
    setCorrectPermission(driveService, copiedFile.getId())
    copiedFile
}

private void setCorrectPermission(Drive driveService, def fileId) {
    Permission newPermission = new Permission();
    newPermission.setType("anyone");
    newPermission.setRole("writer");
    driveService.permissions().create(fileId, newPermission).execute();
}

The problem is that copied spreadsheet has broken add-on (isn't displayed in add-ons menu). There is correct add-on code in script editor but when I try to run any function I get error message 

"We're sorry, a server error occurred. Please wait a bit and try again"

Keep in mind that the very same code work well in my template spreadsheet. Even if I delete all the code and leave empty onOpen function the error still appears.

Copying add-ons works well when I do it using regular google drive website (drive.google.com) and also worked when I tried to use google's API Explorer (https://developers.google.com/drive/v3/reference/files/copy#try-it). The problem seems to only when using sdk (at least java one - I haven't tried any other)

Also keep in mind I'm using google service account created as described in this article https://developers.google.com/identity/protocols/OAuth2ServiceAccount#creatinganaccount

and creating Drive instance with following code

 Drive getDriveService() throws GeneralSecurityException, IOException, URISyntaxException {
    HttpTransport httpTransport = new NetHttpTransport();
    JacksonFactory jsonFactory = new JacksonFactory();
    GoogleCredential credential = new GoogleCredential.Builder()
            .setTransport(httpTransport)
            .setJsonFactory(jsonFactory)
            .setServiceAccountId(G_SERVICE_EMAIL)
            .setServiceAccountScopes(Arrays.asList(DriveScopes.DRIVE))
            .setServiceAccountPrivateKeyFromP12File(PKC_12_FILE)
            .build();
    Drive service = new Drive.Builder(httpTransport, jsonFactory, null)
            .setHttpRequestInitializer(credential)
            .build();


    return service;
}

not sure if it matters though, especially since everything else seems to work just fine

Any fix ideas? I'm open to any workarounds as long as they work. Also I would be fine with creating new file and just adding add-on code but it seems like I can't do it with API

Upvotes: 5

Views: 1397

Answers (2)

Rafał Pydyniak
Rafał Pydyniak

Reputation: 539

I've found an acceptable solution which I described there How can I create spreadsheet with included gs script by API?

Hope it helps someone ;)

Upvotes: 1

Juan Diego Antezana
Juan Diego Antezana

Reputation: 912

Following the comment in How can I create spreadsheet with included gs script by API?, this is the app Scripts solution for making an authenticated POST:

function sendToHR(url,data){
  var forDriveScope = DriveApp.getStorageUsed(); //needed to get Drive Scope requested
  var dataToSend = [getName(),getID()];
  for(key in data){
    dataToSend.push(data[key])
  }
  var paylod = {
    "data" : dataToSend
  };
  paylod = JSON.stringify(paylod);
  var param = {
       "method":"POST",

       "headers"     : {"Accept":"application/json","Authorization": "Bearer " + ScriptApp.getOAuthToken()}, 
       "payload": paylod
      };
  return UrlFetchApp.fetch(url,param).getContentText();
}

And I have an example in python, that might be a bit more useful to you, in order for the python script to execute as a certien user I downloaded a JSON file with the keys from the projects console -> create credential -> get Key and download the file

def get_service(): global http_auth global delegated_credentials

scopes = ['https://www.googleapis.com/auth/userinfo.email']
keyfile = os.path.join(CURR_DIR, JSON_FILENAME)
credentials = ServiceAccountCredentials.from_json_keyfile_name(
keyfile, scopes=scopes)
delegated_credentials = credentials.create_delegated(ADMIN_EMAIL)
http_auth = delegated_credentials.authorize(Http())
return build('SERVICE', 'v1', http=http_auth,
            discoveryServiceUrl='DISCOVERY API SERVICE')

ADMIN_EMAIL is the actual admin email address and CURR_DIR and JSON_FILENAME are related to the downloaded file in your case I'm guessing you dont need admin rights just download the JSON file from the console of your current project and use your email address. Mine works when using the discovery API but a regular POST should be a bit faster to make

Upvotes: 0

Related Questions