google sheet: make a local copy of image link from a shared sheets

Question

my frient shared his google sheet to me and the table contains image which is a link (url). How can i make a copy of this sheet and make all the image link to be local, so i want the image is copying to my local google drive automatically (so the link won't be broken if he delete his images files in future). Right now, if i make a copy of this document, then it still link to original image source.

How is it possible ? of course i don't want to manually copy them one by one from the link. Is there any better and faster way ?

https://docs.google.com/spreadsheets/d/1TkXwAd8rKbjnGfYEJVaOYBJwCZ7G7YfuSvmcDE6g8No/edit?usp=sharing

Answer 1

The OP wants to extract the image URL from a hyperlink formula, and save a copy of the image to their own Google Drive account.

This answer combines several elements from precedents on StackOverflow.

• Since the images metadata is in the formula, the code uses the getFormulas() method rather than the "conventional" getValues(). Cells with no formula are empty strings; hence the test if (formula.length !=0){.

• Get the file name without extension: REGEX: Capture Filename from URL without file extension. Ironically, this precedent doesn't use regular expressions but finds the position of the last / and the last . using lastIndexOf and getting a substring between those points. Note this solution fails on filenames with multiple periods, though there is an alternative solution for this scenario.

• Get the file name from the url: Getting a Google Spreadsheet Cell's Image URL which combines regex and Javascript match.

• Save a file to Google Drive: Need sheets script to save img to drive which is a simple and elegant solution for saving files.

• Saving the file to Google Drive: When copying files using Apps Script from one folder to another any “Apps Script” files being copied end up in MyDrive not the specified folder - why? explains why the API is required to write the files to My Drive.

• Note: In order to use this script, enable Drive API v2 at Advanced Google Services

  • On script editor, Resources -> Advanced Google Services; Turn on Drive API v2

---

function so5811567402() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = "Table";
  var sh = ss.getSheetByName(sheetName);
  var rg=sh.getDataRange();
  var lastColumn = sh.getLastColumn();
  var lastRow = sh.getLastRow();

  var formulas = rg.getFormulas();

  for (var i in formulas) {
    for (var j in formulas[i]) {
      var formula = formulas[i][j];
      if (formula.length !=0){

        var regex = /image\("(.*)"/i;
        var matches = formula.match(regex);
        var imgurl = matches[1];
        var filename = imgurl.substring(imgurl.lastIndexOf("/") + 1, imgurl.lastIndexOf("."));
        //Logger.log(filename);

        var image = UrlFetchApp.fetch(imgurl).getBlob().getAs('image/jpeg').setName(filename);

        var FolderId = "Folder ID goes here";
        var folder = DriveApp.getFolderById(FolderId);
        var file = DriveApp.createFile(image);
        Drive.Files.update({"parents": [{"id": folder.getId()}]}, file.getId());
      }
    }
  }
}