A comprehensive guide to UrlFetchApp in Apps Script
The UrlFetch API allows you to access websites and web applications from Apps Script. It is a very useful API since it lets you connect your Google Sheets spreadsheet to the rest of the digital world.
5 examples of use cases for UrlFetchApp
The use cases below showcase the different ways in which you can use UrlFetchApp in Apps Script:
Downloading files that are accessible via a URL using Apps Script
Checking to see if your website is up or down
Fetching data from another API
Uploading data from your Google Sheets spreadsheet to another application like Notion, Airtable, Evernote or Coda
Prerequisites
This tutorial assumes that you're familiar with:
Google Sheets
Basic coding concepts (If you're new to coding, please check out a series of tutorials that I've written on learning to code using Google Sheets and Apps Script.)
What will you learn in this tutorial?How does the UrlFetch API work?
The UrlFetch API is used to make HTTP requests to URLs. For example, you can use UrlFetch to make GET or POST requests. The information at the URL is then returned in response to the request. The information returned can then be processed by your script.
What is an API?
API stands for Application Programming Interface. APIs enable applications to talk to one another. Learn more
The function fetchExample() below fetches the content at the URL https://example.com and logs it.
function fetchExample() {
let response = UrlFetchApp.fetch("https://example.com");
Logger.log(response);
}When you run the above function, you should see the contents of the webpage https://example.com logged:
<!doctype html>
<html>
<head>
<title>Example Domain</title>
<meta charset="utf-8" />
<meta http-equiv="Content-type" content="text/html; charset=utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<style type="text/css">
body {
background-color: #f0f0f2;
margin: 0;
padding: 0;
font-family: -apple-system, system-ui, BlinkMacSystemFont, "Segoe UI", "Open Sans", "Helvetica Neue", Helvetica, Arial, sans-serif;
}
div {
width: 600px;
margin: 5em auto;
padding: 2em;
background-color: #fdfdff;
border-radius: 0.5em;
box-shadow: 2px 3px 7px 2px rgba(0,0,0,0.02);
}
a:link, a:visited {
color: #38488f;
text-decoration: none;
}
@media (max-width: 700px) {
div {
margin: 0 auto;
width: auto;
}
}
</style>
</head>
<body>
<div>
<h1>Example Domain</h1>
<p>This domain is for use in illustrative examples in documents. You may use this
domain in literature without prior coordination or asking for permission.</p>
<p><a href="https://www.iana.org/domains/example">More information...</a></p>
</div>
</body>
</html>Configuring the UrlFetch request
The fetchExample() function above used the fetch() method of the UrlFetchApp class to fetch content at the URL https://example.com. This is the simplest way to use the UrlFetch API.
You can also use the UrlFetch API to make advanced requests by passing an additional configuration parameter. This parameter is an object that specifies the detailed configuration for how the request should be made.
function fetchExampleUsingConfig() {
let config = {
muteHttpExceptions: true,
method: "get"
};
let response = UrlFetchApp.fetch("https://example.com", config);
Logger.log(response);
}In the code above, the config object specifies how the request should be made. In this case, it specifies that HTTP exceptions should be muted and that the request should be made using the GET HTTP method.
The table below documents the various options for configuring the UrlFetch request. Please also refer to the official documentation:
Name | Type | Description | Notes |
|---|---|---|---|
contentType | String | the content type (defaults to 'application/x-www-form-urlencoded'). Another example of content type is 'application/xml; charset=utf-8'. | This option is typically useful only for requests made using the POST method. |
headers | Object | a JavaScript key/value map of HTTP headers for the request | |
method | String | the HTTP method for the request: get, delete, patch, post, or put. The default is get. | The two most common methods are GET for retrieving data from a URL and POST for uploading or submitting content to a URL. |
payload | String | the payload (that is, the POST body) for the request. Certain HTTP methods (for example, GET) do not accept a payload. It can be a string, a byte array, a blob, or a JavaScript object. A JavaScript object is interpreted as a map of form field names to values, where the values can be either strings or blobs. | A payload is only relevant for POST requests and it is the content that you want to submit or upload using a POST request. |
validateHttpsCertificates | Boolean | If false the fetch ignores any invalid certificates for HTTPS requests. The default is true. | Please be very careful when setting this to |
followRedirects | Boolean | If false the fetch doesn't automatically follow HTTP redirects; it returns the original HTTP response. The default is true. | |
muteHttpExceptions | Boolean | If true the fetch doesn't throw an exception if the response code indicates failure, and instead returns the HTTPResponse. The default is false. | Set |
escaping | Boolean | If false reserved characters in the URL aren't escaped. The default is true. |
How to set a header when making requests using UrlFetchApp?
A HTTP header is used to exchange additional information between your program and the server that hosts the web page. Headers consist of key-value pairs. These key-value pairs are specified as an object associated with a property named headers.
What is an Object?
In Apps Script, objects are used to organize the values in your program that are related to one another. An object is a value that contains other values.
For example, the value student below contains information about other students. Here, student is an Object. Learn more in the tutorial: Objects in Apps Script.
let student = {
firstName : "Megan",
lastName : "Shelton",
section : "A",
grade : "preschool"
}let config = {
headers: {
"key1": value1,
"key2": value2
}
}In the code below, we're pretending that we are going to access content from an API that requires authentication. In this case, the API requires us to set two headers: API-KEY and API-SECRET. The API will check to see if these headers are valid before returning data.
Note: The URL http://httpbin.org/headers simply returns the headers that are set in the request. Hence, it is a great way to check if you're setting headers correctly.
function requestWithHeaders() {
let config = {
headers: {
"API_KEY": "ABC-DEF-GHIJ",
"API-SECRET": "URL-FETCH-IS-AWES0ME"
}
};
let response = UrlFetchApp.fetch("http://httpbin.org/headers", config);
Logger.log(response.getContentText());
}Note: An object in Apps Script can contain other objects. Here, the config object has a property called headers which in turn has an object as its value.
When you run the function requestWithHeaders(), you'll see a log that contains the headers that were received by the httpbin.org server. If API-KEY and API-SECRET are present in the response, it means these were successfully sent as headers. You will likely also see other headers that you did not set in your code. This is because some information is automatically sent as headers whenever you make a HTTP request to a server. For example, the Accept-Encoding header tells the server if it can compress the content it sends back when it responds to your request.
{
"headers": {
"Accept-Encoding": "gzip, deflate",
"Api-Key": "ABC-DEF-GHIJ",
"Api-Secret": "URL-FETCH-IS-AWES0ME",
"Host": "httpbin.org",
"User-Agent": "Mozilla/5.0 (compatible; Google-Apps-Script; beanserver; +https://script.google.com; id: UyqqET731EICQlEXHw)"
}
}When should you set muteHTTPExceptions to true?
Let's say the URL we want to fetch content from is incorrect. Consider the URL: https://raw.githubusercontent.com/spreadsheetdev/httpexamples/master/files/doesnotexist.pdf. This URL is incorrect and you can test this by opening the above link in your browser. You should see the following error when you open it: "404: Not Found". The error code 404 in HTTP indicates that the URL you're trying to access was not found on the server.
The fetchIncorrectFilename() below is using a configuration where muteHttpExceptions is set to false. This means that HTTP errors will NOT be muted and your code will fail when a HTTP error occurs.
function fetchIncorrectFilename() {
let config = {
muteHttpExceptions: false
};
let url = "https://raw.githubusercontent.com/spreadsheetdev/httpexamples/master/files/doesnotexist.pdf";
let response = UrlFetchApp.fetch(url, config);
Logger.log(response.getResponseCode());
Logger.log(response.getContentText());
}When you run the above code, you'll see an error that tells you that the request failed.
Now, let's change the configuration to mute HTTP exceptions. If you run fetchIncorrectFilename() now, the code will not error out. Instead, you will see that the server returned a 404 response with the message "404: Not Found". This is exactly what you will see in your browser when you open the URL manually.
function fetchIncorrectFilename() {
let config = {
muteHttpExceptions: true
};
let url = "https://raw.githubusercontent.com/spreadsheetdev/httpexamples/master/files/doesnotexist.pdf";
let response = UrlFetchApp.fetch(url, config);
Logger.log(response.getResponseCode());
Logger.log(response.getContentText());
}
When should you set muteHttpExceptions to false and when should you set it to true?
If you want your code to fail when the HTTP request fails, then set muteHttpExceptions to false. On the other hand, if you know that your code might try to access a URL that does not exist OR if your goal is to check whether a website is working or not and take some action based on this information, then set muteHttpExceptions to true.
For example, consider an application that allows users to enter a URL that it then fetches content from. Users could make mistakes (typos) when entering the URL. In this case, you might want to set muteHttpExceptions to true so your code does not fail completely. Instead, your code can see what the response from the server is and then display user-friendly messages if the request errors out.
In short, if your application needs to handle scenarios where the UrlFetch could fail, then you should set muteHttpExceptions to true.
Examples on how to use UrlFetchApp to download files
So far we've seen how to use UrlFetchApp to retrieve the contents of a web page. You can also use UrlFetchApp to download files.
The fetchUrl() function accepts the url as input and fetches the content at that url. As you'll see below, this function can be used to download different types of files.
💡Tip
The function fetchUrl() saves the downloaded file to Google Drive using the saveBlobToDrive() function. Please refer to the tutorial on exporting Google Sheets to learn how to send it as an email attachment.
function fetchUrl(url) {
let config = {
muteHttpExceptions: true
};
let response = UrlFetchApp.fetch(url, config);
let blob = response.getBlob();
return blob;
}The function saveBlobToDrive() saves the downloaded file to Google Drive and moves the file to the folder specified by <FOLDER_ID>.
How to get the ID of a folder in Google Drive?
You can get the ID of a folder in Google Drive from its URL. Open the folder in Google Drive and look for the string that follows https://drive.google.com/drive/u/1/folders/. This string is the ID of the folder. In the URL below, the ID of the folder is 1nBsaeGM9_aa12MudkCuYdtCiWsNo.
https://drive.google.com/drive/u/1/folders/1nBsaeGM9_aa12MudkCuYdtCiWsNofunction saveBlobToDrive(blob) {
let file = DriveApp.createFile(blob);
file.moveTo(DriveApp.getFolderById("<FOLDER_ID>"));
}Reminder:
Remember to replace <FOLDER_ID> with the actual ID of the folder in Google Drive where you want downloaded files saved.
The examples below illustrate the fact that the above code can be used to download different types of files. Bel
Download CSV files using UrlFetchApp
The fetchCSV() function downloads a CSV file and saves it to Google Drive.
function fetchCSV() {
let blob = fetchUrl("https://raw.githubusercontent.com/spreadsheetdev/httpexamples/master/files/student_grades.csv");
saveBlobToDrive(blob);
}Download PDF files using UrlFetchApp
The fetchPDF() function downloads a PDF file and saves it to Google Drive.
function fetchPDF() {
let blob = fetchUrl("https://raw.githubusercontent.com/spreadsheetdev/httpexamples/master/files/certificate_template.pdf");
saveBlobToDrive(blob);
}Download Microsoft Presentation (PPTX) files using UrlFetchApp
The fetchPPTX() function downloads a Microsoft Powerpoint (PPTX) file and saves it to Google Drive.
function fetchPPTX() {
let blob = fetchUrl("https://raw.githubusercontent.com/spreadsheetdev/httpexamples/master/files/certificate_template.pptx");
saveBlobToDrive(blob);
}Download images using UrlFetchAppDownload PNG images
The fetchPNG() function downloads a PNG image and saves it to Google Drive.
function fetchPNG() {
let blob = fetchUrl("https://raw.githubusercontent.com/spreadsheetdev/httpexamples/master/files/certificate_template.png");
saveBlobToDrive(blob);
}Download JPEG images
The fetchJPEG() function downloads a JPEG image and saves it to Google Drive.
function fetchJPEG() {
let blob = fetchUrl("https://raw.githubusercontent.com/spreadsheetdev/httpexamples/master/files/certificate_template.jpg");
saveBlobToDrive(blob);
}Download SVG files using UrlFetchApp
The fetchSVG() function downloads a SVG file and saves it to Google Drive.
function fetchSVG() {
let blob = fetchUrl("https://raw.githubusercontent.com/spreadsheetdev/httpexamples/master/files/certificate_template.svg");
saveBlobToDrive(blob);
}How to use UrlFetchApp to download JSON data?
In addition to downloading the content of web pages and files, UrlFetchApp can also be used to download data from APIs. A popular format for downloading data is JSON. Let me explain what JSON is and then how to download JSON data using UrlFetchApp.
What is JSON?
JSON stands for JavaScript Object Notation. It is used to exchange structured data between applications. The table below shows the JSON representation of a JavaScript object. The column on the left has an object containing information about a student. The column on the right shows you the JSON representation of this object.
You'll notice that both the JavaScript object and the JSON representation look almost identical. However, an important difference is that JavaScript objects only exist in the computer's memory. JSON, on the other hand, is a textual representation of data. Therefore, unlike JavaScript objects, you can store JSON data in Google Drive. In fact, a common way to store JavaScript objects in databases is by converting them to JSON first.
Apps Script (i.e. JavaScript ) object | JSON representation of the object |
|---|---|
The student object below contains information about a student. | The same student object converted to JSON. |
Using UrlFetchApp to download JSON data
The function fetchJSON() fetches JSON data from github and saves it to Google Drive. In addition, it also parses this data. That is, it converts the textual representation of the data into a JavaScript object that your code can work with. Finally, the JavaScript object is logged.
Note:
The JSON.parse() method is used to convert data stored in the JSON format into JavaScript objects that your code can process.
function fetchJSON() {
let blob = fetchUrl("https://raw.githubusercontent.com/spreadsheetdev/httpexamples/master/files/country_codes.json");
saveBlobToDrive(blob);
Logger.log(JSON.parse(blob.getDataAsString()));
}How to use UrlFetchApp to submit data to a URL using POST requests?
Up until now, we've been using the GET method (which is also the default method) of UrlFetchApp to retrieve data from URLs. Now let's use the POST method to do the opposite, which is to submit data to a URL.
The function submitDataUrlFetch() submits a person's name and age to the URL http://httpbin.org/anything. This URL will return whatever data you submit to it. So, it's a good way to check if your code is submitting data correctly.
function submitDataUrlFetch() {
let url = "http://httpbin.org/anything";
let config = {
"method": "post",
"payload": {
"name": "Ray C",
"age": 29
}
};
let response = UrlFetchApp.fetch(url, config);
Logger.log(response);
}When you run the function submitDataUrlFetch(), you should see output like this logged:
{
"args": {},
"data": "",
"files": {},
"form": {
"age": "29.0",
"name": "Ray C"
},
"headers": {
"Accept-Encoding": "gzip, deflate",
"Content-Length": "19",
"Content-Type": "application/x-www-form-urlencoded",
"Host": "httpbin.org",
"User-Agent": "Mozilla/5.0 (compatible; Google-Apps-Script; beanserver; +https://script.google.com; id: UAEmdDd-kjkj87fg-qET731EICQlEXHw)"
},
"json": null,
"method": "POST",
"origin": "2101:153:4a02:4270:1192:98d0:5c91:53c4, 106.138.185.6",
"url": "http://httpbin.org/anything"
}Notice that the form property of the object that is returned contains the information you submitted to http://httpbin.org/anything. You'll also notice that the files property is empty. That's because we did not submit any file. Let's try doing that next.
How to use UrlFetchApp to upload files?
The function submitFileUrlFetch() gets the Google Sheets spreadsheet that the Apps Script file is bound to (i.e. the spreadsheet you opened the script editor from) as a PDF file and uploads it to http://httpbin.org/anything.
All you have to do to upload a file is to get it as a blob and then include it in the object associated with the payload property of the UrlFetch configuration.
What is a blob in Apps Script?
Just like JSON is a text based representation of a JavaScript object, a blob is a format that various Apps Script APIs use to exchange data. For example, MailApp knows how to process a blob to attach it as an email attachment. DriveApp knows how to save a blob as a file in Google Drive.
Warning
Always be careful when uploading files to external websites (URLs or APIs). Please replace http://httpbin.org/anything with a website that you trust OR ensure you upload a file that does not contain any sensitive information. For example, the spreadsheet I used to test the code below was an empty spreadsheet.
function submitFileUrlFetch() {
let url = "http://httpbin.org/anything";
let pdfFile = SpreadsheetApp.getActive().getAs("application/pdf");
let data = {
"filename": "Monthly sales report",
"author": "Ray C",
"file": pdfFile
};
let config = {
"method": "post",
"payload": data
};
let response = UrlFetchApp.fetch(url, config);
Logger.log(response);
}When you run the submitFileUrlFetch() function, you will see the files property populated since you uploaded a file along with the request.
{
"args": {},
"data": "",
"files": {
"file": "data:application/pdf;base64,Ci9SZXNvdXJjZXMKOAowClIKL0Fubm90cwoxMAowClIKL0dyb3VwCjw8Ci9TCi9UcmFuc3BhcmVuY3kKL0NTCi9EZXZpY2VSR0IKPj4KPj4KZW5kb2JqCjcKMApvYmoKPDwKL0ZpbHRlcgovRmxhdGVEZWNvZGUKL0xlbmd0aAo5CjAKUgo+PgpzdHJlYW0KeJxtT8sOwiAQvO+/QLcklnj0Ezx5Nia2BzBW/z9xX7RbYggwy87OMCuMEYFXUJDPie5HhRUw5pO0NkDPjc73lGOCbHzfMG7glhzUxq0oViQtQo+NtMANXiLX9mcWZRZTwMDAwMCBmIAowMDAwMDAwMDE2IDAwMDAwIG4gCjAwMDAwMDAxNjAgMDAwMDAgbiAKMDAwMDAwMDIzMSAwMDAwMCBuIAowMDAwMDAwMzk3IDAwMDAwIG4gCjAwMDAwMDA3MjcgMDAwMDAg=="
},
"form": {
"author": "Ray C",
"filename": "Monthly sales report"
},
"headers": {
"Accept-Encoding": "gzip, deflate",
"Content-Length": "1721",
"Content-Type": "multipart/form-data; boundary=\"-----IIewBi58SBIRJlv3kqjww219ax4v5CSvv\"",
"Host": "httpbin.org",
"User-Agent": "Mozilla/5.0 (compatible; Google-Apps-Script; beanserver; +https://script.google.com; id: UAEmdDd-kjkj87fg-qET731EICQlEXHw)"
},
"json": null,
"method": "POST",
"origin": "2101:153:4a02:4270:1192:98d0:5c91:53c4, 106.138.185.6",
"url": "http://httpbin.org/anything"
}Conclusion
In this tutorial, you learned how to use the UrlFetch API in apps script to fetch content from, or submit content to a URL. You also learned how to download files from a URL or upload files to a URL. Hope this tutorial was helpful.
Thanks for reading!