k0sti · November 14, 2019 15:08 · walkergv · Dec 17, 2019
diff --git a/pulautin.gs b/pulautin.gs
 function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Korttipulautin')
      .addItem('Luo Ilmiökortit', 'createScenarioCards')
      .addItem('Luo Linssikortit', 'createLensCards')
      .addToUi();
 }

 function getNamedRange(name) {
  var sheet = SpreadsheetApp.getActive();
  var named = sheet.getNamedRanges().filter(function(r) {return r.getName()==name});
  if (named.length==0) throw("no match "+name)
  return named[0].getRange().getValue();
 }

 function createScenarioCards() {
  createCards(getNamedRange("ScenarioSlideId"), 'Ilmiökortit!A1:H');
 }

 function createLensCards() {
  createCards(getNamedRange("LensSlideId"), 'Linssikortit!A1:H');
 }

 function createCards(templateId, rangeSpec) {

  var sheet = SpreadsheetApp.getActive();
  //SpreadsheetApp.getUi().alert("Slides: "+templateId);

  var content = sheet.getRange(rangeSpec).getValues();
  var headers = content.filter(function(e){return e[0]=="Gen"}); 
  if (headers.length==0) {
    throw("Dokumentista puuttuu otsikon määrittävä Gen-solu");
  }
  headers = headers[0].slice(1);
  content = content.filter(function(e){return e[0]===true});
  content = content.map(function(e){return e.slice(1)});
  
  Logger.log("Header row: "+headers);
  //Logger.log("Content: "+content);
  Logger.log("Row count "+content.length);
  Logger.log("Image count: "+sheet.getImages().length);

  var doc;
  try {
    doc = SlidesApp.openById(templateId);
  } catch (err) {
    throw("Slide-dokumenttia ei löydy: "+templateId);
  }
  var slides = doc.getSlides();
  var templateSlide = slides[0]; 
  var colCount = headers.length;

  Logger.log("Old slide count: "+slides.length+", remove old slides");
  for (var i = 1; i<slides.length; ++i) {
    slides[i].remove();
  }

  for(var i = content.length; i-->0;) {
    var row = content[i];
    var imageUrl = row[headers.indexOf("Kuvan URL")];
    //Logger.log("image: "+imageUrl);

    var newSlide = templateSlide.duplicate();
    if (imageUrl) {
      newSlide.getImages()[0].replace(imageUrl);
    }
    
    for (var col = 0; col<colCount; ++col) {
      var key = headers[col];
      newSlide.replaceAllText("#"+key+"#", row[col]);
    }
  }

  var slidesUrl = "https://docs.google.com/presentation/d/"+templateId;
  //SpreadsheetApp.getUi().alert("Kortit luotu: "+slidesUrl);
  openUrl(slidesUrl);

 }


 function showAnchor(name,url) {
  var html = '<html><body><a href="'+url+'" target="blank" onclick="google.script.host.close()">'+name+'</a></body></html>';
  var ui = HtmlService.createHtmlOutput(html)
  SpreadsheetApp.getUi().showModelessDialog(ui,"demo");
 }

 function openUrl(url){
  var html = HtmlService.createHtmlOutput('<html><script>'
  +'window.close = function(){window.setTimeout(function(){google.script.host.close()},9)};'
  +'var a = document.createElement("a"); a.href="'+url+'"; a.target="_blank";'
  +'if(document.createEvent){'
  +'  var event=document.createEvent("MouseEvents");'
  +'  if(navigator.userAgent.toLowerCase().indexOf("firefox")>-1){window.document.body.append(a)}'                          
  +'  event.initEvent("click",true,true); a.dispatchEvent(event);'
  +'}else{ a.click() }'
  +'close();'
  +'</script>'
  // Offer URL as clickable link in case above code fails.
  +'<body style="word-break:break-word;font-family:sans-serif;">Kortit luotu. <a href="'+url+'" target="_blank" onclick="window.close()">Avaa kortit.</a></body>'
  +'<script>google.script.host.setHeight(40);google.script.host.setWidth(410)</script>'
  +'</html>')
  .setWidth( 90 ).setHeight( 1 );
  SpreadsheetApp.getUi().showModalDialog( html, "Avataan ..." );
 }
	function onOpen() {
	var ui = SpreadsheetApp.getUi();
	ui.createMenu('Korttipulautin')
	.addItem('Luo Ilmiökortit', 'createScenarioCards')
	.addItem('Luo Linssikortit', 'createLensCards')
	.addToUi();
	}

	function getNamedRange(name) {
	var sheet = SpreadsheetApp.getActive();
	var named = sheet.getNamedRanges().filter(function(r) {return r.getName()==name});
	if (named.length==0) throw("no match "+name)
	return named[0].getRange().getValue();
	}

	function createScenarioCards() {
	createCards(getNamedRange("ScenarioSlideId"), 'Ilmiökortit!A1:H');
	}

	function createLensCards() {
	createCards(getNamedRange("LensSlideId"), 'Linssikortit!A1:H');
	}

	function createCards(templateId, rangeSpec) {

	var sheet = SpreadsheetApp.getActive();
	//SpreadsheetApp.getUi().alert("Slides: "+templateId);

	var content = sheet.getRange(rangeSpec).getValues();
	var headers = content.filter(function(e){return e[0]=="Gen"});
	if (headers.length==0) {
	throw("Dokumentista puuttuu otsikon määrittävä Gen-solu");
	}
	headers = headers[0].slice(1);
	content = content.filter(function(e){return e[0]===true});
	content = content.map(function(e){return e.slice(1)});

	Logger.log("Header row: "+headers);
	//Logger.log("Content: "+content);
	Logger.log("Row count "+content.length);
	Logger.log("Image count: "+sheet.getImages().length);

	var doc;
	try {
	doc = SlidesApp.openById(templateId);
	} catch (err) {
	throw("Slide-dokumenttia ei löydy: "+templateId);
	}
	var slides = doc.getSlides();
	var templateSlide = slides[0];
	var colCount = headers.length;

	Logger.log("Old slide count: "+slides.length+", remove old slides");
	for (var i = 1; i<slides.length; ++i) {
	slides[i].remove();
	}

	for(var i = content.length; i-->0;) {
	var row = content[i];
	var imageUrl = row[headers.indexOf("Kuvan URL")];
	//Logger.log("image: "+imageUrl);

	var newSlide = templateSlide.duplicate();
	if (imageUrl) {
	newSlide.getImages()[0].replace(imageUrl);
	}

	for (var col = 0; col<colCount; ++col) {
	var key = headers[col];
	newSlide.replaceAllText("#"+key+"#", row[col]);
	}
	}

	var slidesUrl = "https://docs.google.com/presentation/d/"+templateId;
	//SpreadsheetApp.getUi().alert("Kortit luotu: "+slidesUrl);
	openUrl(slidesUrl);

	}


	function showAnchor(name,url) {
	var html = '<html><body><a href="'+url+'" target="blank" onclick="google.script.host.close()">'+name+'</a></body></html>';
	var ui = HtmlService.createHtmlOutput(html)
	SpreadsheetApp.getUi().showModelessDialog(ui,"demo");
	}

	function openUrl(url){
	var html = HtmlService.createHtmlOutput('<html><script>'
	+'window.close = function(){window.setTimeout(function(){google.script.host.close()},9)};'
	+'var a = document.createElement("a"); a.href="'+url+'"; a.target="_blank";'
	+'if(document.createEvent){'
	+' var event=document.createEvent("MouseEvents");'
	+' if(navigator.userAgent.toLowerCase().indexOf("firefox")>-1){window.document.body.append(a)}'
	+' event.initEvent("click",true,true); a.dispatchEvent(event);'
	+'}else{ a.click() }'
	+'close();'
	+'</script>'
	// Offer URL as clickable link in case above code fails.
	+'<body style="word-break:break-word;font-family:sans-serif;">Kortit luotu. <a href="'+url+'" target="_blank" onclick="window.close()">Avaa kortit.</a></body>'
	+'<script>google.script.host.setHeight(40);google.script.host.setWidth(410)</script>'
	+'</html>')
	.setWidth( 90 ).setHeight( 1 );
	SpreadsheetApp.getUi().showModalDialog( html, "Avataan ..." );
	}