CODEWITHSUNDEEP
google-apps-script
Anjesh Kafle
Anjesh Kafle

Reputation: 11

How can I copy script files from one google sheet to another programmatically?

I have written an app for a client, but there are about a hundred sheets which need the exact same code. Is there a short cut to manually copying/ pasting code into the script editor of each sheet separately?

Upvotes: 0

Views: 1095

Answers (2)

Rob
Rob

Reputation: 575

I for sure wouldn't be copying the code into 100 different spreadsheets. I would create (or use the script that you have already created) as a library that you can add to the sheets that you want it to run on. That way if any changes are needed or bug testing done you don't have to change it on 100 sheets.

You could (should?) also look into deploying an add-on since, if the sheets are duplicated, replicated, or shared, it can wreak havoc if there are any permissions that are required for the current user of the sheet. Setting it up as an add on allows the permissions to be added once instead of sheet-by-sheet.

If you must put the script on every sheet and the sheets are EVER duplicated, make sure that the person duplicating them is the owner of the script and the one that has permissions to run the libraries.

I have many users and thousands of spreadsheets that run the same code and I created a system that automatically generates spreadsheets based on user input or if a new file is added to a google drive directory. This allows the sheet to be owned by me (always) and I don't run into the permissions nightmare that is created if end-users re-create a sheet that references code or libraries that require permissions.

It might sound like I'm making a big deal out of the permissions problem but the biggest issue is that from the Google Sheet side, if the permissions don't exist, there are no error codes that show up at all -- it just doesn't work for the end-user and then they get frustrated, and you get frustrated, and any changes that you make might require new permissions. Trust me, the permissions issue is something to look into with this.

Upvotes: 2

Cooper
Cooper

Reputation: 64040

Here's a sample:

I doubt that's it's bullet proof so it may need some additional work but it seems to be working for me.

It launches a dialog that allows you to enter source and destination ids and the files you want to copy. Note the destination cannot have files of the same name.

function appendSelectedFilesDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('appendSelected1'), 'Append Selected Files');
}

function appendSelectedFilesIntoAnotherProject(obj) {
  const srcProjectId = obj.srcProjectId;
  const dstProjectId = obj.dstProjectId;
  const srcFileNames = obj.srcFileNames;
  var all = true;
  var fA = [];
  if (srcFileNames.length > 0) {
    all = false;
    fA = String(srcFileNames).split(',').map(n => n.trim());
  }
  if (srcProjectId && dstProjectId) {
    const base1 = "https://script.googleapis.com/v1/projects/"
    const url1 = base1 + srcProjectId + "/content";
    const url2 = base1 + dstProjectId + "/content";
    const options1 = { "method": "get", "muteHttpExceptions": true, "headers": { "Authorization": "Bearer " + ScriptApp.getOAuthToken() } };
    const res1 = UrlFetchApp.fetch(url1, options1);
    const data1 = JSON.parse(res1.getContentText());
    const srcfiles = data1.files;
    const options2 = { "method": "get", "muteHttpExceptions": true, "headers": { "Authorization": "Bearer " + ScriptApp.getOAuthToken() } };
    const res2 = UrlFetchApp.fetch(url2, options2);
    const data2 = JSON.parse(res2.getContentText());
    const dstFiles = data2.files;
    srcfiles.forEach(file => {
      if (file.source && file.name) {
        if (!all) {
          if (fA.indexOf(file.name) > -1) {
            dstFiles.push(file);
          }
        } else {
          dstFiles.push(file);
        }
      }
    });
    const request = { "files": dstFiles };
    const base = "https://script.googleapis.com/v1/projects/"
    const url = base + dstProjectId + "/content"
    const options = { "method": "put", "request": request, "muteHttpExceptions": true, "headers": { "Authorization": "Bearer " + ScriptApp.getOAuthToken() }, "contentType": "application/json", "payload": JSON.stringify(request) };
    const resp = UrlFetchApp.fetch(url, options);
    console.log(resp);
    SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput('<textarea rols="12" cols="60">' + resp + '</textarea>'), "Update Script Project");
  }
  return { "message": "Process Complete" };//minimally to remove the dialog
}

html:

!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>input{margin:2px 5px 2px 0;}</style>
  </head>
  <body>
    <form>
      <input type="text" id="src" name="srcProjectId" size="60" placeholder="Enter Source Project Id" />
      <br /><input type="text" id="dst" name="dstProjectId" size="60" placeholder="Enter Destination Project Id" />
      <br /><input type="text" id="names" name="srcFileNames" size="60" placeholder ="Enter Desired Files separated by commas or nothing if you want all files." />
      <br /><input type="button" value="Submit" onClick="process(this.parentNode);" />
    </form>
    <script>
      function process(obj) {
        google.script.run
        .withSuccessHandler(function(obj){google.script.host.close();})
        .appendSelectedFilesIntoAnotherProject(obj);
        console.log('Form:',JSON.stringify(obj));
      }
    </script>
  </body>
</html>

Upvotes: 0

Related Questions