Use JavaScript to connect to your Google Spreadsheet data Query your Sheet data using JavaScript output to webpage
https://www.udemy.com/course/sheet-data-ajax/
Google Sheets Output Data AJAX
This code is a JavaScript program that retrieves data from a Google Sheet and displays it on a web page. Let’s break down the code step by step:
const sheetID = ‘1jnlfz6PVstmEc’;
const base = `https://docs.google.com/spreadsheets/d/${sheetID}/gviz/tq?`;
These lines define the ID of the Google Sheet that the program will retrieve data from, and a base URL for making requests to that sheet. The base URL includes the sheet ID and some additional parameters to specify the data query.
const sheetName = ‘users’;
let qu = ‘Select A,C,D WHERE D > 150’;
qu = ‘Select * WHERE B =”Svekis”‘;
qu = ‘Select * WHERE A contains “Jo”‘;
qu = ‘Select * WHERE A contains “Jo”‘;
qu = ‘Select * WHERE E > date “2021-12-31″‘;
qu = ‘Select * WHERE C = “active” And B = “Svekis”‘;
These lines define the query that will be used to retrieve data from the Google Sheet. Each line updates the qu variable with a different query. The queries use the SQL-like syntax that Google Sheets supports for data queries.
const query = encodeURIComponent(qu);
const url = `${base}&sheet=${sheetName}&tq=${query}`;
These lines construct the final URL that will be used to retrieve data from the Google Sheet. The encodeURIComponent function is used to encode the query string, and the resulting URL includes the sheet name and query parameters.
const data = [];
document.addEventListener(‘DOMContentLoaded’, init);
These lines create an empty array to hold the data retrieved from the Google Sheet, and add an event listener for when the page has finished loading. The init function will be called when the page has finished loading.
function init() {
console.log(‘ready’);
fetch(url)
.then(res => res.text())
.then(rep => {
//console.log(rep);
const jsData = JSON.parse(rep.substr(47).slice(0, -2));
console.log(jsData);
const colz = [];
jsData.table.cols.forEach((heading) => {
if (heading.label) {
colz.push(heading.label.toLowerCase().replace(/\s/g, ”));
}
})
jsData.table.rows.forEach((main) => {
//console.log(main);
const row = {};
colz.forEach((ele, ind) => {
//console.log(ele);
row[ele] = (main.c[ind] != null) ? main.c[ind].v : ”;
})
data.push(row);
})
maker(data);
})
}
This is the init function that is called when the page has finished loading. It retrieves data from the Google Sheet by making a request to the URL constructed earlier, and then processes the data and passes it to the maker function. The data is first parsed as a JSON object, and then the column headings are extracted and converted to lowercase with spaces removed. Each row of data is then processed and converted to a JavaScript object, with the column headings used as the object properties. The resulting objects are then added to the data array.
function maker(json) {
const div = document.createElement(‘div’);
div.style.display = ‘grid’;
output.append(div);
let first = true;
json.forEach((el) => {
//console.log(ele);
const keys = Object.keys(el);
div.style.gridTemplateColumns = `repeat(${keys.length} ,1fr)`;
if (first) {
first = false;
keys.forEach((heading) => {
const ele = document.createElement(‘div’);
ele.textContent = heading.toUpperCase();
ele.style.background = ‘black’;
ele.style.color = ‘white’;
div.append(ele);
})
}
keys.forEach((key) => {
const ele = document.createElement(‘div’);
ele.style.border = ‘1px solid #ddd’;
ele.textContent = el[key];
div.append(ele);
})
console.log(keys);
})
}
The maker function takes in a JSON object as an argument, and it creates a grid display of the data contained in the JSON object.
First, the function creates a div element, sets its display property to grid and appends it to the output element. Then, it loops through the JSON object with forEach and for each element, it gets the keys of that element with Object.keys(el), which returns an array of keys.
Next, the function sets the gridTemplateColumns property of the div element to a CSS grid template string that repeats the number of columns in the JSON object.
If it is the first iteration of the loop (determined by the first variable), the function creates a div element for each key in the array and appends it to the div element. The text content of each div is set to the uppercase version of the key, with a black background and white text color.
Finally, for each key in the array, the function creates another div element and sets its textContent property to the value of that key in the current element of the JSON object. The border property of this div element is set to a 1-pixel solid gray color.
All of the div elements created are appended to the div element created at the beginning of the function, resulting in a grid display of the JSON data in the output element. The keys of each element are displayed as the column headings, and the values are displayed in each row.
- The code starts by defining a constant variable sheetID that stores the ID of a Google Spreadsheet document. This ID is used to access the document’s data through its API.
- The next line defines a constant base variable that stores a string URL, which is used as the base URL to access the Google Spreadsheet data API. The sheetID variable is appended to this URL to create the full URL for accessing the Spreadsheet.
- A constant variable sheetName is defined to store the name of a specific sheet within the Google Spreadsheet document.
- A variable qu is defined and initialized with a SQL-like query string that selects specific data from the Google Spreadsheet. This query string is then modified multiple times to query different sets of data from the sheet.
- The encodeURIComponent function is used to encode the qu variable’s value to a format that can be used in a URL.
- The url variable is defined by concatenating the base, sheetName, and query variables, creating a complete URL for accessing the data from the Google Spreadsheet document’s API.
- An empty array called data is initialized.
- The code then adds an event listener that waits for the DOM to be loaded, which then triggers the init function.
- The init function is then called, which first logs a message to the console to confirm that the function has been called.
- A fetch request is made to the url variable defined earlier. The fetch method returns a promise that is resolved with the response object. This response object is then converted to text format using the text method.
- The then method is called on the result of the text method. This method takes a callback function that parses the response text using the JSON.parse method. The parsed data is then stored in the jsData variable.
- The colz array is then initialized as an empty array.
- The forEach method is used to loop through the jsData.table.cols array and check if each column heading label exists. If it does, the label is added to the colz array in lowercase format with spaces replaced with empty strings.
- Another forEach method is used to loop through each row of data in jsData.table.rows and create a new object for each row. Each column of data in the row is then added to the corresponding key in the new object.
- The new objects created in the previous step are then pushed to the data array.
- The maker function is then called, which takes the data array as an argument.
- The maker function creates a new div element and sets its display property to grid.
- The div element is then appended to the output element.
- The forEach method is used to loop through each row of data in the json parameter passed to the function.
- The Object.keys method is used to get an array of all the keys in the current row object.
- The gridTemplateColumns property of the div element is set to repeat the number of columns in the row object.
- If this is the first row being processed, the function creates a new div element for each key in the row object, sets its text content to the uppercase version of the key, and styles it with a black background and white text color.
- For each key in the row object, the function creates a new div
Use JavaScript to connect to your Google Spreadsheet data Query your Sheet data using JavaScript output to webpage
https://www.udemy.com/course/sheet-data-ajax/
Explore how you can connect to your Google Sheets Spreadsheet using JavaScript. Use your sheet data on your webpage query your sheet content and output it to web pages.
Recent changes to Spreadsheet data output now allows you to use your Google Sheet Data similar to a Database where you can query the data and return the results. This is a perfect coding solution for frontend developers to get access to data, with AJAX retrieve JSON data and output it to web pages. Data is live from your Google Spreadsheet, retrieve the data and output it on the page.
Fast paced lessons that are designed for those with previous JavaScript experience!
Objective of the lessons is to provide example code as to how you can use AJAX to connect to your Google Spreadsheet data and query the sheet data from the Frontend JavaScript code. No server, no backend coding no database required.
Taught by an instructor with many years of web development experience ready to help you learn more about JavaScript and Google Sheets.
Several students had asked me for a set of lessons to show how to connect to Google Sheets and return the results with AJAX to their web pages. This course contains all the source code and step by step lessons to make an AJAX request for Sheet data.
Source Code
const sheetID = ‘1jnlfz6PV8zGmE2tjAn97LG7J-6m4r2ffa75B75stmEc’;
const base = `https://docs.google.com/spreadsheets/d/${sheetID}/gviz/tq?`;
const sheetName = ‘users’;
let qu = ‘Select A,C,D WHERE D > 150’;
qu = ‘Select * WHERE B =”Svekis”‘;
qu = ‘Select * WHERE A contains “Jo”‘;
qu = ‘Select * WHERE A contains “Jo”‘;
qu = ‘Select * WHERE E > date “2021-12-31″‘;
qu = ‘Select * WHERE C = “active” And B = “Svekis”‘;
const query = encodeURIComponent(qu);
const url = `${base}&sheet=${sheetName}&tq=${query}`;
const data = [];
document.addEventListener(‘DOMContentLoaded’, init);
const output = document.querySelector(‘.output’);
function init() {
console.log(‘ready’);
fetch(url)
.then(res => res.text())
.then(rep => {
//console.log(rep);
const jsData = JSON.parse(rep.substr(47).slice(0, -2));
console.log(jsData);
const colz = [];
jsData.table.cols.forEach((heading) => {
if (heading.label) {
colz.push(heading.label.toLowerCase().replace(/\s/g, ”));
}
})
jsData.table.rows.forEach((main) => {
//console.log(main);
const row = {};
colz.forEach((ele, ind) => {
//console.log(ele);
row[ele] = (main.c[ind] != null) ? main.c[ind].v : ”;
})
data.push(row);
})
maker(data);
})
}
function maker(json) {
const div = document.createElement(‘div’);
div.style.display = ‘grid’;
output.append(div);
let first = true;
json.forEach((el) => {
//console.log(ele);
const keys = Object.keys(el);
div.style.gridTemplateColumns = `repeat(${keys.length} ,1fr)`;
if (first) {
first = false;
keys.forEach((heading) => {
const ele = document.createElement(‘div’);
ele.textContent = heading.toUpperCase();
ele.style.background = ‘black’;
ele.style.color = ‘white’;
div.append(ele);
})
}
keys.forEach((key) => {
const ele = document.createElement(‘div’);
ele.style.border = ‘1px solid #ddd’;
ele.textContent = el[key];
div.append(ele);
})
console.log(keys);
})
}