0

I'd like to manage spreadsheets (uploaded by a client via API) over google engine. I created test spreadsheet extended by functions in .gs script (which works great if I add them via online editor) but I do not know how to apply such a .gs script to every uploaded spreadsheet automatically (possibly via API).

Moreover, I do not want to share this .gs file by publishing it as an extension (because of security policies).

Updated: I also tried copying existing spreadsheet with working gs script and overwriting its spreadsheet data via API (instead of uploading new one) - it's overwritten data successfully but unfortunately lost gs script. Details are described in separate (bounty) question: Custom google app script doesn't work after copying spreadsheet with google java client

Community
  • 1
  • 1
Michal_Szulc
  • 4,097
  • 6
  • 32
  • 59
  • not possible unless you make copies of a master spreadsheet instead of creating new plain spreadsheets – Zig Mandel Jun 24 '16 at 14:27
  • Did you read my *Update*? I've tried it, but it's still not working. – Michal_Szulc Jun 24 '16 at 14:45
  • scripts do not get lost after updating data. clarify if the script gets copied or not, and exactly how you copy it (code) – Zig Mandel Jun 24 '16 at 14:50
  • I used this code: http://stackoverflow.com/questions/37967100/custom-google-app-script-doesnt-work-after-copying-spreadsheet-with-google-java – Michal_Szulc Jun 24 '16 at 18:42
  • include relevant code in the question. – Zig Mandel Jun 24 '16 at 18:45
  • @ZigMandel are you just trolling? if so - stop. I'm looking for a solution, not for lols. Code of copying spreadsheets (but erasing .gs script) is in provided question. I do not have a code of creating spreadsheet with .gs by API - I'm asking for it here. – Michal_Szulc Jun 24 '16 at 19:45
  • 2
    see my profile and s.o. guidelines and decide for yourself. trying tp help you meet them. the most relevant part about your question is missing * in the question.* – Zig Mandel Jun 24 '16 at 19:47
  • I have a script that copies a Template spreadsheet that has a BOUNDED script, that works fine but I've noticed that if you copy the spreadsheet to another Drive instance, the script is lost. Maybe a workaround could be to: copy the template spreadsheet on the same Drive instance and than change the permissions and give proprietary rights to the account of the other Drive account? – Juan Diego Antezana Jun 30 '16 at 15:37
  • @JuanDiegoAntezana Actually I'm copying to same drive instance but it still doesn't work - see this topic http://stackoverflow.com/questions/37967100/custom-google-app-script-doesnt-work-after-copying-spreadsheet-with-google-java – Rafał Pydyniak Jul 01 '16 at 06:28
  • Here is an idea, I have this App Scripts code that is currently working, coping a template spreadsheet that has a bounded script to it: Why not create a webapp with apps script and call it from your java app? Say you send a POST with the template ID ? – Juan Diego Antezana Jul 01 '16 at 08:16
  • `function copySheet(){ var templateID = 'your_template_ID'; /*----------------------------COPY TEMPLATE------------------- create the HR Sheet from template */ var folder = DriveApp.getFolderById(folderID).createFolder(); var copySheet = DriveApp.getFileById(templateID).makeCopy(folder).setName('This_is_the_copy'); //add stuff to your new sheet with copySheet obj }` – Juan Diego Antezana Jul 01 '16 at 08:16
  • @JuanDiegoAntezana I'm fine with creating and using external webapp but just one question - are you using service account? I've got a feeling that there is some kind of bug when using service account files. I've even tried using raw HTTP methods to authenticate and copy a file but I still can't make it work – Rafał Pydyniak Jul 01 '16 at 10:29
  • Yeah for the HTTP post you should go though an authorization header, you can use a generated one from the projects console and passing the token as "bearer" . I'll post an example of what I did in python and also in app scripts – Juan Diego Antezana Jul 01 '16 at 12:12

3 Answers3

2

So I've even tried copying using plain HTTP methods since I thought it might be a problem with Java SDK but it still didn't work which means that there is some problem at google side (or something is really unclear) with service accounts.

After all I solved the problem with another approach.

First I copy spreadsheet with script attached to it to my service account (as I described in this Custom google app script doesn't work after copying spreadsheet with google java client topic). The file is copied with broken script.

Then I make user authorize using google's oauth (this tutorial shows how https://developers.google.com/identity/protocols/OAuth2WebServer#overview) And then on his behalf I perform copying.

Finally the file is on his private drive which is the biggest downside of this solution but at least the script works which was my main goal.

Hope it helps ;)

Community
  • 1
  • 1
Rafał Pydyniak
  • 539
  • 4
  • 11
0

You can publish a google script without it going on the Marketplace. If you make a Google group you can publish in such a way that only members of your google group can see it. If your google group only has you, only you can see it. Then it will be on all of your spread sheets.

The downside being that google will never let you remove it from their server, only unpublish it.

EvSunWoodard
  • 1,270
  • 1
  • 10
  • 28
0

You can create a stand-alone script that is independent of any spreadsheet. This script would contain any methods/functions needed to create and manipulate spreadsheets according to your use case. Furthermore you could deploy the script as a web-app and implement a doGet() or doPost() method. Whenever a user uploads spreadsheet data via API you can call the web app with the information and have it create spreadsheets as needed.

user16195
  • 36
  • 1