how would you set up to trigger generation and display of a QR code using the following process:
THE FOLLOWING PORTION OF THE PROCESS IS BUILT AND SUCCESSFULLY PERFORMING URL SHORTEN AND CUSTOMIZATION AND POPULATING SHORT URL IN SPREADSHEET.
- a form is submitted
- data change triggers my app script function to call bitly
- bitly shortens and customizes longurl of the submitted forms detail view
- POSTS the custom short URL back the spreadsheet
CONTEXT
- In my app an authenticated inventory manager completes a form to add equipment to the inventory SS.
- once form is saved it makes available the forms detail view to include equipment specifications, information, and a item specific QR code.
- The qr code is scanned so that there is a transparent activity log that shows administrators who is in possesion of the equipment > who the provider / reciever was accounting for the 5 most recent transactions, and provides identification and damage tracking via an add image INPUT available during the transaction process.
[ in summary i need the QR code image to be viewable and scannable in the detail view post creation of the bitly short URL. ]
THIS IS WHERE I HIT A ROADBLOCK.
ive tried so many different App Scripts and tried to query Gen AI and have read and re-read all bitlys QR generation documentation and i guess im just not seeing something but i can't seem to figure out how to
- use the newly populated Bitly Short Url to generate and store a QR img in Google drive
- GET and POST the Images Drive URL to the QR code column in the spreadsheet so it is viewable in the application.
THIS LAST PART IS A MAJOR FEATURE DEPENDENCY.
Essentially once a piece of equipment is added to the inventory an employee will be required to scan the Qr Code listed in the equipments detail view and check it out to enforce equipment tracking and accountability .
below is my app script functions. i have it split to accommodate specific steps of the process so it ensures the dependent data is populated before proceeding to the next step. Also i have helper functions not directly called from appsheet but generate dependent constructors used in the functions themselves.
ShortURL
const properties = PropertiesService.getScriptProperties().getProperties();
const apiKey = properties['API_KEY'];
const domain = properties['DOMAIN'];
const group = properties['GROUP_GUID'];
const baseUrl = properties['BASE_URL']
const drive = properties['FOLDER_ID']
function shortenURL(longUrl) {
const apiEndpoint = `https://api-ssl.bitly.com/v4/shorten`;
const options = {
'method': 'POST',
'headers': {
'Authorization': 'Bearer ' + apiKey,
'Content-Type': 'application/json'
},
'payload': JSON.stringify({
'long_url': longUrl,
'domain': domain,
'group_guid': group
})
};
const response = UrlFetchApp.fetch(apiEndpoint, options);
const data = JSON.parse(response.getContentText());
// Log the short link and QR code link
Logger.log('Short Link: ' + data.link);
return {
shortLink: data.link
};
}
Helper functions
GET QR ID from generated short link
function getQRID(shortLink) {
const url = `https://api-ssl.bitly.com/v4/bitlinks/` + shortLink;
const headers = {
'Authorization': apiKey,
};
const response = UrlFetchApp.fetch(url, { headers: headers });
if (response.getResponseCode() === 200) {
const data = JSON.parse(response.getContentText());
return data.id;
} else {
throw new Error("Error retrieving shortlink data");
}
}
Get the bitly qr code image link created in parallel with the shortLink by Bitly
function getQRImg(shortLink) {
const qrCodeId = getQRID(shortLink);
const url = `https://api-ssl.bitly.com/v4/bitlinks/` + qrCodeId + `/qr`;
const headers = {
'Authorization': apiKey,
'Content-Type': 'application/json' // Set content type for JSON payload
};
// Create payload object with the QR code URL
const payload = {
qr_code: qrCodeId
};
const options = {
'method': 'post',
'headers': headers,
'payload': JSON.stringify(payload)
};
const response = UrlFetchApp.fetch(url, options);
if (response.getResponseCode() === 200) {
const data = JSON.parse(response.getContentText());
const qrCodeUrl = data.qr_code;
Logger.log("QR Image URL:", qrCodeUrl);
Logger.log("Full QR Code Data:", data);
// Fetch QR image data
const imgResponse = UrlFetchApp.fetch(qrCodeUrl);
// **Log for Debugging:**
Logger.log("QR Image Response Code:", imgResponse.getResponseCode());
Logger.log("QR Image URL:", qrCodeUrl);
if (imgResponse.getResponseCode() !== 200) {
throw new Error(`Error fetching QR image: ${imgResponse.getContentText()}`);
}
const blob = imgResponse.getBlob();
return blob;
} else {
throw new Error("Error fetching QR image");
}
}
Upload Qr code image to google drive and add drive link to QRLink column in spreadsheet and viewable by referencing url in the QRImg column of the same row
function generateAndSaveQR(shortLink) {
try {
// 1. Create the QR Code and get the QR code ID
const qrCodeId = getQRID(shortLink);
// 2. Fetch the QR code image
const blob = getQRImg(shortLink);
// 3. Save to Google Drive
const folder = DriveApp.getFolderById(driveFolderId);
const fileName = `${shortLink}.png`;
const file = folder.createFile(fileName, blob);
Logger.log("QR Code Image saved to Google Drive:", file.getUrl());
return {
filePath: file.getUrl(), // Return the full file path
fileName: file.getName()
};
} catch (error) {
Logger.log(`Error generating QR code and image: ${error.message}`);
return null;
}
}
the entire automation on Appsheets triggers, as well as app script returning bitly short link to spreadsheet working great but there is a disconnect when trying to get the actual qr image that populates with the bitly shorlink to upload an image file to drive and return the drive file link back into the associated row.
And all the logging and debugging messages when the automation runs will provide is the following error message:
"Error generating QR code and image: Error fetching QR image"
LinkedIn
Twitter
