0

I have data in a google sheet that want to use to drive the creation of a google slide. I successfully created a working template for the first row of my google sheet. This takes a copy of an existing slide template and then creates a hexagon shape and plots it on the slide driven by the data in the slide. However I need the script to iterate or loop through the rows/ range of the sheet and populate the slide with multiple hexagon shapes.

[Google Sheet data][1] [Desired outcome][2]

I just don't have the knowledge to work this out, any help would be much appreciated

  SpreadsheetApp.getUi()
      .createMenu('Templates')
      .addItem('HeatMap', 'createNewCase')
      .addToUi();
}

  function createNewCase(input) {
   var category = input
   var ui = SpreadsheetApp.getUi();

    // get active spreadsheet
    var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = activeSpreadsheet.getActiveSheet();



    //Get active cell

    var sourceRow = activeSheet.getActiveCell().getRow();



    // Read case variables

    var sourceTeam = activeSheet.getRange('A' + sourceRow).getValue();
    var sourceRisk = activeSheet.getRange('C' + sourceRow).getValue();
    var sourceRef = activeSheet.getRange('B' + sourceRow).getValue();
    var sourceWidth = activeSheet.getRange('E' + sourceRow).getValue();
    var sourceHeight = activeSheet.getRange('F' + sourceRow).getValue();
     var sourceColour = activeSheet.getRange('G' + sourceRow).getValue();



    // Message prompt

  var userChoice = ui.alert("You are about to generate a Risk Heatmap for " + sourceTeam +"\n\nDo you wish to continue?\n\n",ui.ButtonSet.YES_NO);
    if (userChoice == ui.Button.NO) {
      ui.alert("Terminating script");
      return;
    } 

    //Get todays date 

    var nowShort = Utilities.formatDate(new Date(), "Europe/London", "yyMMdd");
    var nowFormattedDateTime = Utilities.formatDate(new Date(), "Europe/London", "EEE d MMM yyyy HH:mm");



    //Set case document filename

    var newFilename = sourceTeam + " Risk Heatmap"

    //Get template folder

    var templateFolder = DriveApp.getFolderById("xxxxxx");

    // Get case Template document

    var docTemplate = DriveApp.getFileById("xxxxxxx"); 
    var caseDocument = docTemplate.makeCopy(newFilename);       
    var caseDocumentID = caseDocument.getId();
    var caseSlide = SlidesApp.openById(caseDocumentID);
    var activeSlide = caseSlide.getSlideById('xxxxxxx')
    var shape = activeSlide.insertShape(SlidesApp.ShapeType.HEXAGON,sourceWidth,sourceHeight,80,40);
    var textRange = shape.getText();
    var textCol =shape.getFill().setSolidFill(sourceColour)
    var insertedText = textRange.appendText(sourceRef +" - "+ sourceRisk);
    insertedText.getTextStyle()
    .setForegroundColor('#000000')
    .setFontSize(8)


    var caseProcess = caseSlide.replaceAllText("%TEAM NAME%", sourceTeam);
    var dateTime = caseSlide.replaceAllText("%DATE%", nowFormattedDateTime);


  // Record Case Document link      
    activeSheet.getRange('H' + (sourceRow)).setFormula('=HYPERLINK("' + caseDocument.getUrl() + '", "Risk Heatmap")');

  }```


  [1]: https://i.stack.imgur.com/tPCuI.png
  [2]: https://i.stack.imgur.com/XVx8M.png
Tom
  • 87
  • 1
  • 9
  • Look at how to use [getRange()](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer,Integer)) to collect multiple rows, getValues() from that range, and how to [loop through the two-dimensional array it gives you](https://stackoverflow.com/questions/52867326/newbie-add-values-to-two-dimensional-array-with-for-loops-google-apps-script) – sinaraheneba May 29 '19 at 10:47
  • I can getRange as you mentioned above but looping through the 2d array I'm just struggling to understand. 8*( – Tom May 29 '19 at 15:03
  • See the examples in the linked thread (or search for others) and show what code you're trying – sinaraheneba May 29 '19 at 15:05

0 Answers0