thewiki

Visitors: 0

By thewiki Editorial 7 Feb 2023

Google Apps ScriptApps ScriptScriptingCodingProgrammingGoogle SlidesGoogle SheetsWeb App

Developing Web Apps using Google App Script is pretty easy, once you have understood the basics of coding. But if you don't have much knowledge about coding, you can still use the below steps to create a web app that can send data entered from google forms to google slides.

This is really helpful when you have to automate data processing or even if you want clients or employees to fill in the details themselves to reduce manual errors.

Below are some of the steps on how you can achieve it;

1. Create a Google Form with all the required fields:
First, ensure that you have created a google form with all the required fields, and also link it to the google sheets. You can do that by going to the response option and clicking on the green sheets icon.

2. Click on Extensions:
Once you have created the google sheet and linked it with the google form, then click on the extensions option in the above ribbon of google sheets and then click on Apps Script.

3. Add the below code in the "code.gs" option:

function onOpen() {
SpreadsheetApp.getUi().createAddonMenu()
.addItem('Update presentation', 'updatePresentation')
.addToUi();
}

function updatePresentation() {
let ui = SpreadsheetApp.getUi();

let presentationURL = ui.prompt('Enter the presentation URL:').getResponseText();
if (!presentationURL) {
return;
}

let presentationId = presentationURL.match(/[-\w]{25,}/)[0];

let row = parseInt(ui.prompt('Enter the row number:').getResponseText(), 10);
if (!row) {
return;
}

let sheet = SpreadsheetApp.getActiveSheet();
let marketData = getDataFromSheet_(sheet, row);
let slides = getSlides_(presentationId);

for (let i = 0; i < slides.length; i++) {
writeDataToPlayerSlide_(slides[i], marketData);
}
}

function getDataFromSheet_(sheet, row) {
let data = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
return data;
}

function getSlides_(presentationId) {
let presentation = SlidesApp.openById(presentationId);
let slides = presentation.getSlides();
return slides;
}

function writeDataToPlayerSlide_(slide, marketData) {
slide.replaceAllText('{{Name}}', marketData[1]);
slide.replaceAllText('{{Address}}', marketData[2]);
slide.replaceAllText('{{CProfession}}', marketData[3]);
slide.replaceAllText('{{Date of Birth}}', marketData[4]);
}

Explanation of the Above Code:

The code is a Google Apps Script that creates a custom menu item in a Google Spreadsheet, which allows users to update data in a Google Slides presentation.

The code consists of 5 functions:

onOpen() adds the "Update presentation" menu item to the Google Spreadsheet.
updatePresentation() prompts the user to enter the URL of the Google Slides presentation and the row number of the data in the Google Spreadsheet. Then it retrieves the data from the specified row, retrieves the slides from the Google Slides presentation, and calls the writeDataToPlayerSlide_() function to replace placeholders in the slides with the data.
getDataFromSheet_() retrieves data from the specified row in the Google Spreadsheet.
getSlides_() retrieves the slides from the Google Slides presentation.
writeDataToPlayerSlide_() replaces placeholders in the slide with the data.

4. Now paste the below code in link.html:

<!DOCTYPE html>
<html>
  <head>
    <title>AppScript Selector</title>
    <style>
      /* Add styles for a beautiful page */
      body {
        font-family: Arial, sans-serif;
        margin: 0;
        padding: 0;
      }

      .header {
        background-color: lightgray;
        display: flex;
        justify-content: space-between;
        padding: 20px;
      }

      .header h1 {
        margin: 0;
        font-size: 24px;
      }

      .header a {
        color: black;
        text-decoration: none;
        font-size: 18px;
        margin-right: 20px;
      }

      .content {
        display: flex;
        flex-wrap: wrap;
        justify-content: space-between;
        padding: 20px;
      }

      .card {
        background-color: lightgray;
        border-radius: 10px;
        box-shadow: 2px 2px 10px rgba(0, 0, 0, 0.2);
        display: flex;
        flex-direction: column;
        align-items: center;
        justify-content: center;
        height: 200px;
        width: 300px;
        margin: 20px;
        text-align: center;
      }

      .card h2 {
        margin: 0;
        font-size: 24px;
      }

      .card a {
        color: black;
        text-decoration: none;
        font-size: 18px;
        margin-top: 20px;
      }
    </style>
  </head>
  <body>
    <header class="header">
      <h1>Please Click the below link to copy the slide</h1>
    </header>
    <main class="content">
      <div class="card">
        <h2>Slide1</h2>
        <a href="#" target="_blank">Click Here</a>
      </div>
      <div class="card">
        <h2>Slide 2</h2>
        <a href="#">Coming Soon</a>
      </div>
      <div class="card">
        <h2>Slide 3</h2>
        <a href="#">Coming Soon</a>
      </div>
    </main>

<p><h3>Step by Step Process to use this tool</h3></p><br>

<li>Click one of the template given above</li><br>
<li>Create a copy of the template that you have selected</li><br>
<li>Open the Google Sheet with Data Input</li><br>
<li>In the Google Sheets Toolbar, click on Extensions & you will be able to see the application name</li><br>
<li>Click on it and you will see "Update Presentation Option"</li><br>
<li>Once you have clicked on Update Presentation, paste the Presentation URL in the dialog box</li><br>
<li>Now this tool will ask you for the row number of the data that you want to publish in the google slide</li><br>
<li>After providing row number, press ok, and your presentation will be updated with the new data</li><br>
<li>Now you can download the file in PDF format</li>

  </body>
</html>

Explanation of the Above Code:
The code defines an HTML document with a webpage design using CSS styles. The webpage includes a header with a title and a main content area that contains three cards, each with a title and a link. The content section of the webpage provides a list of steps for using a tool in a Google Sheets extension.

5. Now to ensure that the above code runs, create a script.gs file and add the below code to the same:

function doGet() {
return HtmlService.createHtmlOutputFromFile('link.html');
}

Explanation of the Above Code:
The doGet function is a function in Google Apps Script that is executed when a user visits a published Google Apps Script web app. The function returns an HTML output created from an HTML file named "link.html". The HTML file will be the visible content of the web app when accessed.

6. Deploy WebApp:
Now once you have added these codes, you can now click on Deploy and then select New Deployment. Once you have done that, it will ask you to select the type of the project, and you can select Web app in the same.

7. Use the link displayed to use your Web App:
As you have deployed your web app, now you should be able to send data from one sheet to another and also will be able to create copies of the slides that will in turn help you in organizing the data.

thewiki

Develop a Google App Script Web Application that can send data from Google Forms to Slides

Topics

thewiki Editorial

Related blogs

Why we confuse Analytics with coding?

What is Data Science?

Quote of the Day