programmatically edit script in Google Drive document

1
votes

I have a Google Drive document which writes values to a Google Drive spreadsheet using Google Apps Scripts.

The script associated with the document looks a lot like this:

// must change value to actual spreadsheet ID
RobProject.spreadsheetID  = "spreadsheetID";

function onOpen()
{
    // do stuff;
}

Each time I create a spreadsheet and its related documents, I manually change the value spreadsheetID to the spreadsheet's ID so the documents know to which spreadsheet they should write their values.

I would like a programmatic way to fill in the correct value for spreadsheetID into the Documents' scripts.

When I search for "edit scripts programmatically," I get tutorials for creating Google Apps Scripts, not editing scripts with scripts. Is there any way to edit Google Apps Scripts with a Google Apps Script?

2
google-apps-script

2 Answers

2
votes

If I understand correctly, you are working with a document and a spreadsheet. The document needs to know the Id of the spreadsheet.

There are some ways to access the Google Apps Script code through the API, but that is only for standalone projects, not for container-bound scripts (unfortunately).

You could consider using a naming convention for the document and spreadsheet so that you could use the Drive service to get from the document to the spreadsheet (DriveApp.getFilesByName()). Or possibly organize them by folder (DriveApp.getFoldersByName(), folder.getFiles()).

If you wanted to store the spreadsheet Id in a project property, you could build a UI in the document that let the user open up the list of files in Drive and pick the associated spreadsheet and then store the Id (ScriptProperties.setProperty('SpreadsheetId')).

1
votes

Don't forget that onOpen has a parameter. You could use following code:

// Define global variable somewhere 
RobProject = {};

function onOpen(e) {

  RobProject.sSheet = e.source; // maybe the spreadsheet object is as useful as the ID
  RobProject.spreadsheetID = e.source.getId();

    // do stuff;
}

Please, for your own sake, don't try to write selfmodifying code.