Forem: Vryntel

Check Links with Google Spreadsheet

Vryntel — Sun, 21 Jul 2024 17:25:59 +0000

In this tutorial I'll show you how you can check multiple links with a Google Spreadsheet and a simple Google Apps Script.

You may find this article useful if you have a lot of links and want to find broken ones, or for example, if you are testing some API endpoints with different parameters, headers, or body and want to check http responses.

Spreadsheet + Google Apps Script:

https://docs.google.com/spreadsheets/d/1UyuKCRO8BWcIaluGEYTuUZglBryo0uFBji2a6tbBXCw/copy

Github Page:

https://github.com/Vryntel/Spreadsheet-Url-Tester

Spreadsheet Structure

The spreadsheet is composed by 2 sheet:

Data
Settings

The Data sheet use the following columns:

Column A: URL
Column B: HTTP request method (Supported HTTP request methods: GET, POST, DELETE, PATCH, PUT)
Column C: params in JSON format

Example:
URL: https://mywebsite.com
PARAMS: {"param1":"test", "params2":"test2"}
FETCHED URL: https://mywebsite.com?param1=test&param2=test2

Column D: headers in JSON format
Column E: Body Type that can be JSON or TEXT
Column F: body in JSON/TEXT format
Column G: Response Code returned by the request
Column H: Response Body returned by the request

In the Settings sheet you can edit some adavnced settings of the HTTP requests:

validateHttpsCertificates: TRUE/FALSE --> If FALSE the fetch ignores any invalid certificates for HTTPS requests
followRedirects: TRUE/FALSE --> If FALSE the fetch doesn't automatically follow HTTP redirects; it returns the original HTTP response

Usage

After making a copy of the spreadsheet, in the top toolbar will appear the option "Url Tester" and then "Test Urls" (if it doesn't appear try to reload the page).

To run the script you need to click Test Urls option. Only the first time it will ask you to grant permission for the script to be executed.

If you only need to use the spreadsheet to check for broken links, you can use only the column A to insert the links and in the column B you can leave the GET method for each url row. When you run the script, the response code will appear in the column G and if it's OK it will be printed the code 200, otherwise another code, you can see all possible codes here:

https://developer.mozilla.org/en-US/docs/Web/HTTP/Status

Some notes:

Google Apps Script has some limitations regarding URL Fetch calls ( at the moment is 20,000 / day). So if you have more than 20,000 URLs to test, consider to split them in more groups and test in multiple days. There are also other limitations regardings headers size, response size ecc. You can find all the limitations here:

https://developers.google.com/apps-script/guides/services/quotas

You can hide (not delete) some columns that you don't need, for example columns C, D, E, F.

How to Duplicate any Google Form

Vryntel — Mon, 29 Jan 2024 11:08:51 +0000

Unlike Google Sheets, Google Form doesn't offer a direct way to make a copy of a public Google Form, without being an owner or a collaborator of the form itself.

As a work-around you can use the FormDuplicator tool, that is a Google Spreadsheet bounded with a Google Apps Script that can clone every public Google Form.

FormDuplicator spreadsheet:
https://docs.google.com/spreadsheets/d/1nYBfvTkIWfmBFhnaNFrDDQh5m_9GJ0m0xHPbqVQhfD4/copy

Github page:
https://github.com/Vryntel/FormDuplicator

After making a copy of the spreadsheet, to be able to run the script you need to grant permission to the script (only the first time) by following these steps:

In the Spreadsheet page, go in the top toolbar and click Extensions > Apps Script
After the new page will open up, return in the spreadsheet page and in the top toolbar it will show up the FormDuplicator option
Click on the FormDuplicator option in the toolbar, and then click on "Duplicate Form"
At this point Google will ask your authorization to run the script, you need to grant it

Now to clone a Google Form, just paste the form url in the "Form to duplicate" field and then go to the top toolbar, FormDuplicator, Duplicate Form.

After the script will finish its execution, a pop-up will show up with a message:

"Form Duplicated correctly": The form has been duplicated exactly as the original form. Your copy of the Google Form will be printed in the "Generated Form" field;
"Form Partially duplicated": The form has been duplicated but without some propeties. All the not duplicated proprieties will be displayed in the "Not duplicated" worksheet;

For the list of what the tool can't duplicate from a form, check my Github page:
https://github.com/Vryntel/FormDuplicator

"Error": If some values are missing in the settings worksheet or an unexpected error has occured;

Thank you for reading.

Google Sheets add a Background Music

Vryntel — Thu, 20 Jan 2022 11:11:11 +0000

In this tutorial I'll show you how to play a music or an audio directly in your sheet.

In Google Sheet it's not possible to directly add a music and play it. But, with the help of the Google Apps Script Platform, we can embed a sidebar in our sheet, and from there add and listen every audio we want.
Could be a music, a relaxing sound effect or any sound you want!

And we can also play it automatically, when the sheet is open so we don't need to click anything. You open the sheet and the music start to play.

Let's start.

First thing you need to create a new sheet, click on "Extensions" in the menu bar, and click on "Apps Script".

Now copy this function that will run automatically when you open your sheet:



function onOpen(e) {
  //create a menu bar element to open the sidebar
  SpreadsheetApp.getUi()
  .createMenu('Music Player')
  .addItem('Show sidebar', 'showSidebar')
  .addToUi();
}

This function creates a new menu called "Music Player" in the menu bar, creates an element inside this menu called "Show sidebar" and assign the function "showSidebar" to the click on this element.



function showSidebar() {
  //create sidebar layout from file Sidebar.html
  var ui = HtmlService.createTemplateFromFile('Sidebar')
    .evaluate()
    .setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setTitle("Music Player");

  SpreadsheetApp.getUi().showSidebar(ui);
}

Now we need to define the layout file of the sidebar.
Create a new HTML file, by clicking the "+" symbol near files, and call this file "Sidebar".



<!-- Use a templated HTML printing scriptlet to import common stylesheet -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

<div class="sidebar branding-below">
  <p>
    Play some chill music.
  </p>
  <audio id="player" controls>
    <source src="http://docs.google.com/uc?export=open&id=XXXXXXXXXXXXXXXXXX" type="audio/mp3">
    Your browser does not support the audio tag.
  </audio>
</div>


<div class="sidebar bottom">
  <span class="gray branding-text">PlayMusic</span>
</div>

Let's also add a new HTML file called "Stylesheet", which includes the CSS Google style for our sidebar:



<!-- This CSS package applies Google styling; it should always be included. -->

<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">

<!-- Customize your audio player -->
<style>
  #player {
    width: 95%;
  }
</style>

The file types supported by the audio tag are MP3, WAV, and OGG (Depending on the browser, some types aren't supported).
So in the source link you can insert any link that end with ".mp3", ".wav" or ".ogg".

If you want to use a Google Drive audio, you need to make the file public by sharing it.

And click on "change to everyone with the link":

You will get a link like this:

https://drive.google.com/file/d/XXXXXXXXXXXXXXXXXX/view?usp=sharing

The XXXXXXXXXXXXXXXXXX string is your file ID. But we can't use this link. To obtain a direct link to the mp3, just add that ID to this link:

http://docs.google.com/uc?export=open&id=XXXXXXXXXXXXXXXXXX

Copy this link in the src attribute.

Now everything is ready. Save your project and reload the sheet page. After the loading is complete, you should see the "PlayMusic" menu.

Click on show sidebar and the audio player will show up in the right.

If you want to play the music automatically, without clicking in the menu bar, you need to add the "autoplay" attribute to the audio tag, in the HTML file (you can also add a loop in your audio, with the "loop" attribute after the autoplay attribute).

<audio id="player" controls autoplay>

Another thing you need to change if you want to autoplay the audio, is the onOpen trigger. This because the "onOpen" function (with this specific name) is a default function that is associated with a simple trigger, but all simple trigger have some limitations, so in this case we need to add our "onOpen" trigger. Before this we can modify the name of the function "onOpen" to "onOpenAutoplay", and add inside it a call to our showSidebar function.



function onOpenAutoplay(e) {
  //create a menu bar element to open the sidebar
  SpreadsheetApp.getUi()
  .createMenu('Music Player')
  .addItem('Show sidebar', 'showSidebar')
  .addToUi();
  //on sheet open, show the sidebar that autoplay the audio inside it
  showSidebar();
}

Last thing we need to add our custom trigger. So go in the trigger section on the left (the timer icon), add a new trigger with these settings:

Save, reload the sheet and enjoy your music :D

Other info:

The first time you run the script, it will ask you to give permissions
If the sheet is in view mode only, only you can run the script and play the music. If you make the sheet editable for everyone with the link, only non-anonymous user (logged account) will be able to run the script.

Google Form countries list dropdown

Vryntel — Tue, 18 Jan 2022 16:35:54 +0000

In this article I’ll show you how you can add a simple dropdown menu with all countries.

Google Form hasn’t a default element to show a list of countries, cities, languages or every other useful list of things. To resolve this problem we can simply copy a list of items (for example from another document) in our form editor answers and create it by ourself.

First thing you need a list of countries. You can grab it from Internet but, to make your work easier, I already created one in this Google Sheet:

https://docs.google.com/spreadsheets/u/1/d/1vDD6D0ogBrA4vOTKhttSUB7wcpNApJ7SBM5AX9oyRTw/edit?usp=sharing

Now you need to copy all the list. You can do this by clicking in the small dropdown arrow near the A column letter:

Click copy.

Now return in the form editor page, click on the first answer of the dropdown and paste all the list.

We created a dropdown menu with all countries.

You can repeat this process with every list you want and you can copy the list in other form elements like checkboxes or multiple choices question.

Add Responses Counter To Your Google Form

Vryntel — Sun, 16 Jan 2022 18:17:42 +0000

In this article I'll show you how to add a responses counter text, directly in the form, by using a simple script.

We will see two different type of counter, an incremental counter that will show how many people submitted our form, and a decremental counter, useful for example when you need to organize a limited event.

Incremental Counter:

Decremental Counter:

To make this, we will use the Google Apps Script Platform.

Create a new form or open one you already have. Add a new title and description element like this:

After this, click on the three dots in the right of the page and click on Script Editor.

Now in the Editor page just copy this code for the incremental counter:



function increaseCounter() {
  // Get the form to which this script is bound.
  var form = FormApp.getActiveForm();

  //Get responses number
  var responses = form.getResponses().length;

  //The position of the question to change, starting from 0
  var questionIndex = 0;

  //Get the question and update the counter
  var question = form.getItems()[questionIndex];
  question.setTitle("This form has been submitted " + responses + " times");

  //To change the title or the description of the form
  //form.setDescription("Counter: " + responses);
  //form.setTitle("Counter: " + responses);
}

And this for the decremental counter:



function decreaseCounter() {
  // Get the form to which this script is bound.
  var form = FormApp.getActiveForm();

  //Get responses number
  var responses = form.getResponses().length;

  var startCounter = 200;
  var currentCounter = startCounter - responses;

  //The position of the question to change, starting from 0
  var questionIndex = 0;

  //Get the question and update the counter
  var question = form.getItems()[questionIndex];
  question.setTitle("Only " + currentCounter + " tickets remaining");

  //To change the title or the description of the form
  //form.setDescription("Counter: " + currentCounter);
  //form.setTitle("Counter: " + currentCounter);
}

You can also update the Form Title and Description, or any other form questions you want, all you need is the position index in the form (just count the questions from the top and starting from 0).

Save the project.

Now we need only to update the counter every time a user submit a new response. In other words we need to add a new event trigger that will run on every form submit action. To add a new trigger click on triggers on the left bar.

Click on Add trigger button, on the bottom right, select the function to run and set the event type to "On form submit".
Click on save.

Now the counter is ready :)