1

We use Google Drive (GAFE) to prepare and present teaching/training materials. We'd like to maintain archived versions of past iterations, and then work on a new copy for each consecutive training session.

I've succeeded in making a copy of our training folder (using ericyd's gdrive-copy), and we're happily working away on that, BUT... the files are fairly heavily cross-linked. The Slides, for instance, will have links to the Docs handouts and PDF assignments associated with that lesson. When I made a copy of the whole folder structure, the files copied over, but the links are still all linked to the original files, when in fact what we want is for them to be linked to their respective copies.

This makes sense - obviously, when you make a copy of a file, you usually don't want to changes its contents at the same time. However, when you're making an archive of a whole folder, ideally you'd like the links within the files to update as well.

I can compile a spreadsheet with the file IDs for each "original and copy" pair. Is there any way to iterate through all Google Docs/Sheets/Slides in a folder, and substitute the original URLs from the spreadsheet file with their respective copy URLs?

I'm practically a beginner when it comes to Google Apps Scripts, so while I have found Get All Links in a Document and am guessing it would be part of the answer, I have no clue where to go beyond that.

(Btw, if there's a different way of going about all three, automating fixing the links in Slides would be the most helpful, as that's where the bulk of them are)

Community
  • 1
  • 1
Alicja Z
  • 249
  • 1
  • 2
  • 9

1 Answers1

0

I know this is a rather old topic, but I recently ran into similar situation that I needed to solve. In my searching, this is the only reference I could find referring to cross-linking as a result of duplication. Unfortunately, I was not able to come up with a purely automated solution, but through a bit of ingenuity I was able to reduce the number of steps required to update my hyperlinks to reference the duplicated files rather than the originals.

First, I borrowed some script code I found online to generate a list of files within a Google Drive folder and their URL's. I'll post the code below. This generates a new Google Sheet named "URL LIST" (you can change the name if you wish in the script), that once generated you'll need to find on your recent list in your Google Drive and move to the folder containing the copied documents and sheets.

Next, in the Google Sheet that I have my hyperlinks to my documents, I created an additional Tab also called URL LIST, and in A1 added an IMPORTRANGE() to import the URL LIST contents. Once you're done with all of this, you will only have to update this one reference with each copy you make, thus dramatically reducing the number of updates you'll need to make, i.e. IMPORTRANGE() points at a specific URL, so each newly generated URL LIST will have a new URL that the copied document containing your hyperlinks and IMPORTRANGE() will need to point to. Hopefully, that makes sense.

Next, your hyperlinks will need a formula along the lines of =HYPERLINK(VLOOKUP(A1,'URL LIST'!$A$1:$B$10,2,FALSE) to grab the imported URL's. It's important to make sure you that you indicate that the look up range is not sorted, or FALSE, because the order that the script spits out the document list with URL's may change depending on how the folder is sorted at the time of running the script, and will ensure you don't need the list sorted. You can then copy the formula to each cell that you need a hyperlink.

Of equal importance is that your VLOOKUP() search key is exactly as it will be listed in your URL LIST.

This method allowed me to reduce the number of steps of updating hyperlinks from 9 steps down to the 1 step of updating the IMPORTRANGE() each time I make copies.

I hope this helps you or someone else!

Copy and past the following script into your script editor:

// replace your-folder below with the folder name for which you want a listing
function listFolderContents() {
  var foldername = 'your-folder';
  var folderlisting = 'URL LIST ';

  var folders = DriveApp.getFoldersByName(foldername)
  var folder = folders.next();
  var contents = folder.getFiles();

  var ss = SpreadsheetApp.create(folderlisting);
  var sheet = ss.getActiveSheet();
  sheet.appendRow( ['name', 'link'] );

  var file;
  var name;
  var link;
  var row;
  while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    link = file.getUrl();
    sheet.appendRow( [name, link] );     
  }  
};
D H
  • 1