0

I want to make a regular backup of a project (default GCP project) that is coded on top of Google Spreadsheet. And on top of the it is a COPY of original SpreadSheet. So the project script file is bound to the spreadsheet.

Bound scripts generally behave like standalone scripts except that they do not appear in Google Drive a quote from above documentation link.

I guess that is the reason why I get an error

Error  GoogleJsonResponseException: API call to drive.files.get failed with error: File not found: 
     12OX6dRqsEHRsR4MttkDQ71yW_I8R2UqjfcSq4FB backupSS  @ web functions.gs:3958

on the 4th line of below code. Note that the scriptId exists but the file is not accessable

var spreadSheetId = SpreadsheetApp.getActiveSpreadsheet().getId()  
var scriptId = ScriptApp.getScriptId()
console.log(Drive.Files.get(spreadSheetId))
console.log(Drive.Files.get(scriptId))

with this result

9:24:12 PM  Notice  Execution started
9:24:12 PM  Info    PR-Digitalizace objednávek
9:24:12 PM  Error   GoogleJsonResponseException: API call to drive.files.get failed with error: File not found: 12OX6dRPKwwt1-vOtCsbDEHRsR4MttkDQ71yW_I8R2Uqq4FB
backupSS    @ web functions.gs:3958

console.log(DriveApp.getFileById(scriptId).getName()) givess me this error `Error   
Exception: No item with the given ID could be found. Possibly because you have not edited this item or you do not have permission to access it.
backupSS    @ web functions.gs:3959`

is there any way to edit scritp name programatically? Why so I do not have lots of script with the same name.

Update

  • Google Apps Script API is on

  • Request had insufficient authentication scopes.

    function getScriptContent(scriptId,content,theAccessTkn) { try{ var options,payload,response,url;

      if (!scriptId) {
        scriptId = ScriptApp.getScriptId()
      }
    
      if (!content) {
        //Error handling function
        console.log("no content")
      }
    
      if (!theAccessTkn) {
        theAccessTkn = ScriptApp.getOAuthToken();
      }
    
      //https://developers.google.com/apps-script/api/reference/rest/v1/projects/updateContent
      url = "https://script.googleapis.com/v1/projects/" + scriptId +"content"
      url = "https://scriptmanagement.googleapis.com/v1/projects/" + scriptId //+"content"
    
    
      options = {
        "method" : "GET",
        "muteHttpExceptions": true,
        "headers": {
          'Authorization': 'Bearer ' +  theAccessTkn
        },
        "contentType": "application/json",//If the content type is set then you can stringify the payload
    //    "payload": JSON.stringify(content)
      };
    
      response = UrlFetchApp.fetch(url,options);      
      console.log('getResponseCode ' + response.getResponseCode())
      console.log("Response content: " + response.getContentText())
      console.log("finished " )
    } catch(e) {
      console.log("Error: " + e + "\nStack: " + e.stack)
    }
    

    };

gives me

    Response content: {
  "error": {
    "code": 403,
    "message": "Request had insufficient authentication scopes.",
    "status": "PERMISSION_DENIED"
  }
}
Radek
  • 13,813
  • 52
  • 161
  • 255
  • You can try using the Apps Script API. It allows you to manage document bound scripts. See documentation: https://developers.google.com/apps-script/api – TheAddonDepot Sep 21 '21 at 17:06
  • Are you renaming the 'Untitled project' or the 'Code.gs'? – NightEye Sep 21 '21 at 18:32
  • @NaziA: the project got a name alredy. But I want to make a copy of the spreadsheet. In such case the project is cloned and then I end up with two projects with the same name. If I create back up every day then .... – Radek Sep 21 '21 at 18:35
  • @Radek, I see. I'm not sure why yours doesn't work as I don't get any errors whatsoever from the snippet above. Although if that still don't work, can you try `DriveApp.getFileById(scriptId).setName('Renamed project')` ? it works on my side. Just make sure to have it refreshed after running to see the change. – NightEye Sep 21 '21 at 18:38
  • I will not work .. my script is bound. Create you script from a spreasheet and try. – Radek Sep 21 '21 at 18:39
  • If what you want is to automatically rename the project after duplicating the spreadsheet, i believe that is still impossible. There is currently no way to get the id of the script bound to a file. https://stackoverflow.com/questions/54990478/google-apps-script-get-the-id-of-script-bound-to-a-spreadsheet – NightEye Sep 21 '21 at 18:49
  • Hi @Radek, I've just confirmed that the code above i provided will work on original files and the script bound to it. BUT it will not work on copied files and its bound script. – NightEye Sep 21 '21 at 19:01
  • there is no difference if the file/project is copied or not. The above code does not work for me even for the original spreadsheet/project. – Radek Sep 21 '21 at 19:03

1 Answers1

1

It seems I can still access the original bound script via DriveApp

Code:

DriveApp.getFileById(scriptId).setName('tester');

Original:

success

But not when running it on the copied file

Copied file:

error

Conclusion:

  • I haven't found any possible way to automatically rename the bound script after duplicating/copying it. Also there's currently no method that allows you to get the ids of the script bound to a file.
  • And if the above code wouldn't work even on original for you, then that would make it even harder.

Resources:

NightEye
  • 10,634
  • 2
  • 5
  • 24
  • is your script bound AND default GCP project ? – Radek Sep 21 '21 at 19:17
  • Yes to both @Radek. As you can see, `SpreadsheetApp.getActiveSpreadsheet().getId()` is returning an ID. – NightEye Sep 21 '21 at 19:19
  • @Radek, `SpreadsheetApp.getActiveSpreadsheet().getId() ` would result into `TypeError: Cannot read property 'getId' of null` if this is stand alone script. – NightEye Sep 21 '21 at 19:20
  • well, then it is not working for me because of something else ... I am using basically – Radek Sep 21 '21 at 19:30
  • console.log(DriveApp.getFileById(scriptId).getName()) givess me this error `Error Exception: No item with the given ID could be found. Possibly because you have not edited this item or you do not have permission to access it. backupSS @ web functions.gs:3959` – Radek Sep 21 '21 at 19:31
  • @Radek, most likely that file is already a copy of a different file. If you create a new sheet and use the code above in a new script bound to it, it should execute properly. Unfortunately, this approach would be useless in your case since you are creating multiple copies and the code above is proven unusable in those cases. – NightEye Sep 21 '21 at 19:33
  • 1
    You are right. I can confirm that the issue is with a copy of the spreadsheet. The bound script then behaves strangely. – Radek Sep 21 '21 at 19:44
  • Feel free to file the issue as bug at issuetracker.google.com @Radek. – NightEye Sep 24 '21 at 18:32