Google app verification lost from google apps script project when sheet is copied

user_5915579 0
Viewed 500

I Developed a google app scripts application that uses a spreadsheet as its database,

I give the app to customers by giving them a URL to make a copy from the spreadsheet (the script is on that spreadsheet) and they authorize and use the copy.

Lately, my customers are getting an "Unverified App" screen before authorizing the script. so I filled out a form for google verification and Google verified it, but my customers still get the unverified screen...

I suspect that the problem is that each sheet gets a new Project Id and client id when copied, so the google verification does not apply to them.

Does anyone know a solution that I can have the copies verified as well? or is there another way to give the app to people without requiring them making a copy and still everybody should have his own script and spreadsheet?

google-apps-script oauth-2.0 google-sheets
1 Answers

Although there is no solution to retain the same project id when making a copy, you can publish your project as a web app and have the web app creating the sheet for you and use that sheet to read and write data.

Here is what I did

   function getCustomSpreadsheet(){
        //try to get the spreadsheet id of the already created spreadsheet
        var ssid = PropertiesService.getUserProperties().getProperty('customSsid');
        //if sheet does not exist, create it
        if(!ssid) var ssid = createCustomSpreadsheet();
        var ss = SpreadsheetApp.openById(ssid);
        return ss;
   }

   function createCustomSpreadsheet(){
        //make a copy of your template spreadsheet and save its id to a property
        fileId = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";//your ss id here
        var ss = DriveApp.getFileById(fileId).makeCopy();
        var ssid = ss.getId();
        PropertiesService.getUserProperties().setProperty('customSsid', ssid);
        return ssid;
  }


I found/replaced everywhere in my code SpreadsheetApp.getActiveSpreadsheet() to getCustomSpreadsheet() and lived happely ever after...

user_59155790
Related