Virtual Gallery

Code.gs

/**
 * @license
 * Copyright 2019 Google LLC
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *     https://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

/**
 * Creates a slide deck of art data from the spreadsheet.
 */
function createVirtualGallery() {
  var spreadsheet = SpreadsheetApp.getActive();
  var artSheet = spreadsheet.getSheetByName('Art Results');
  var artDataRange = artSheet.getDataRange();
  
  // Use the Named Ranges in the Gallery Sheet for the Intro slide values.
  var artistName = spreadsheet.getRangeByName('ArtistName').getValue();
  var medium = spreadsheet.getRangeByName('Medium').getValue();
  var artist_bio = spreadsheet.getRangeByName('ArtistBio').getValue();
  
  // There may not be as many pieces as requested by the user.
  var numArtSlides = artDataRange.getNumRows() - 1;
  console.log('%s %s %s', artistName, numArtSlides, medium);
  
  var artSlideId = copyArtistSlidesTemplate(artistName, medium);  
  var presentation = SlidesApp.openById(artSlideId);
  var introSlide = presentation.getSlides()[0];
  introSlide.replaceAllText('{{artist_name}}', artistName);
  introSlide.replaceAllText('{{medium}}', medium);
  introSlide.replaceAllText('{{artist_bio}}', artist_bio);
  
  if(numArtSlides > 1) {
    copyArtTemplateSlides(presentation, numArtSlides)
  }
  fillArtSlides(presentation, artDataRange);
}

/** 
 * Creates a copy of the template with a title using the Artist's name and medium.
 */
function copyArtistSlidesTemplate(artistName, medium) {
  var copyTitle = artistName + '\'s ' + medium + 's-' 
    + Utilities.formatDate(new Date(), "GMT+1", "yyyy-MM-dd");
  
  var driveResponse = DriveApp.getFileById(SLIDES_TEMPLATE_ID).makeCopy(copyTitle)
  return driveResponse.getId();
}

/**
 * The template comes with one art slide, copy enough for the count.
 */
function copyArtTemplateSlides(presentation, numArtSlides) {
  var artTemplateSlide = presentation.getSlides()[1];
  for (var i = 1; i < numArtSlides; i++) {
    presentation.insertSlide(i, artTemplateSlide);
  }
}

/**
 * Merges the art images and metadata into the slides.
 */
function fillArtSlides(presentation, artDataRange) {
  var artDataRange = artDataRange.getValues();
  for (var i = 1; i < artDataRange.length; i++) {
    var currentSlide = presentation.getSlides()[i];
    var row = artDataRange[i];
    
    var title = row[TITLE_COLUMN];
    currentSlide.replaceAllText('{{title}}', title);
    
    var startDate = row[BEGIN_DATE_COLUMN];
    currentSlide.replaceAllText('{{start_date}}', startDate);
    
    var endDate = row[END_DATE_COLUMN];
    currentSlide.replaceAllText('{{end_date}}', endDate);
    
    var imageUrl = row[IMAGE_URL_COLUMN];
    insertImage(presentation, currentSlide, imageUrl, title);
  }
}

/** 
 * Size adjusts based on the slides dimensions and inserts image into the given slide
 * relative to the title length.
 */
function insertImage(presentation, slide, imageUrl, title) {
  // Convert the GCS URL to an image URL.
  imageUrl = imageUrl.toString().replace('gs://', 'http://storage.googleapis.com/')
  // Top and left values will be overwritten but use this to resize.
  try {
    var image = slide.insertImage(imageUrl, 200, 200, 275, 275);
  } catch(e) {
    var image = slide.insertImage(NOT_FOUND_IMAGE, 200, 200, 275, 275);
    console.log("IMAGE URL: " + imageUrl)
  } 
  var imgWidth = image.getWidth();
  var imgHeight = image.getHeight();
  var pageWidth = presentation.getPageWidth();
  var pageHeight = presentation.getPageHeight();
  var newX = pageWidth/2. - imgWidth/2.;
  var newY = pageHeight/2. - imgHeight/2.;
  
  // If title is long, push the image down further on slide.
  var yOffset = 10;
  if (title.toString().length > 50) {
    yOffset += 12
  }
  // If title is REALLY long, push the image down further on slide
  if (title.toString().length > 65) {
    yOffset += 22
  }
  image.setLeft(newX).setTop(newY + yOffset);
}

Constants.gs

// The art deck template.
var SLIDES_TEMPLATE_ID = '<TEMPLATE_ID>';

// Not found image for invalid image URLs.
var NOT_FOUND_IMAGE = 'https://www.maxpixel.net/static/photo/2x/Page-Not-Found-404-Link-Rot-Broken-Link-2367103.png';

// Art Sheet constants.
var TITLE_COLUMN = '0';
var BEGIN_DATE_COLUMN = '4'
var END_DATE_COLUMN = '5'
var IMAGE_URL_COLUMN = '6';