CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Timothy Kenny
Timothy Kenny

Reputation: 131

Insert Image (from URL) from Google Sheet Cell to Google Doc

This code works for inserting an image

But I want to fetch the url from a google sheet. I put "UrlFetchApp.fetch("sourceSheet.getActiveCell().getValue()");"

in the code below to show my thinking on how to solve this...

  function insertImage() {



  // Retrieve an image from the web.
  var resp = UrlFetchApp.fetch("sourceSheet.getActiveCell().getValue()");

  // Create a document.
  var doc = DocumentApp.openById("1qWnGAR_WBpHkSdY5frld0VNfHtQ6BSzGxlzVNDi5xMk");

  // Append the image to the first paragraph.
  doc.getChild(2).asParagraph().appendInlineImage(resp);
}

The end goal is that I have a sheet with the ID of the doc in one column and the ulr of the image to insert in the other column. I want the script to run so that I can insert each image into each doc.

Upvotes: 4

Views: 3463

Answers (2)

Timothy Kenny
Timothy Kenny

Reputation: 131

This builds on Eduardo's Script (Thx Eduardo) and adds the feature of iteratively going through the spreadsheet and doing this process on every row. To do this I made it so that you can insert a different image in a different doc on each row.

Since AutoCrat doesn't support inserting images like this yet, this is the best work around I have found.

To test this script I put lastRow = 3. Change 3 to sheet.getLastRow() to do your full sheet.

Also note that "getsheetbyname" uses single quotes 'XXX' because it has a space in it, which breaks this otherwise.

function insertImage() {
    var startRow = 2;  // First row of data to process
    var lastRow = 3;   // Last row of data to process

    for (var i = startRow; i <= lastRow; i++)
    {
        // Get the Spreadsheet where the url is defined
        var sheet = SpreadsheetApp.openById("0AsrSazSXVGZtdEtQOTFpTTFzWFBhaGpDT3FWcVlIasd").getSheetByName('88. Research');


        // Get the target document.
        var docid = sheet.getRange("F"+i).getValue();

        var doc = DocumentApp.openById(docid);

        // Get the url from the correct cell
        var url = sheet.getRange("D"+i).getValue();

        // Retrieve an image from the web.
        var resp = UrlFetchApp.fetch(url);

        // Append the image to the first paragraph.
       doc.getChild(0).asParagraph().appendInlineImage(resp.getBlob());
    }

}

// replace 3 with sheet.getLastRow()

Upvotes: 3

Eduardo
Eduardo

Reputation: 22834

Here's a possible solution. Of course you have to replace the Document and Spreadsheet ids with your own document ids.

function insertImage() {
  // Get the target document.
  var doc = DocumentApp.openById("1DeAGfM1PXXXXXXXXXXXXXXXXXXwjjeUhhZTfpo");

  // Get the Spreadsheet where the url is defined
  var sheet = SpreadsheetApp.openById("0Agl8XXXXXXXXXXXXXXXXXXXXXXXXXRScWU2TlE");

  // Get the url from the correct celll
  var url = sheet.getRange("A1").getValue();

  // Retrieve an image from the web.
  var resp = UrlFetchApp.fetch(url);

  // Append the image to the first paragraph.
  doc.getChild(0).asParagraph().appendInlineImage(resp.getBlob());
}

Upvotes: 7

Related Questions