Google Sheet + Google Drive = 🤯 - Part 2

In this article, I will start to deploy my tinyCMS where I store pet project info and how to get it through the API. Here is the sketch design for my solution if you have not read or remember

1. Env & Key

First of all, we need to set up environment and key to development. We would need two things (Google Drive + Google Sheets), for sure 😁 . I will separate it into two sections

1.1 - Google Drive

1. Create a new folder
2. Change permission access to public for editor (you can follow image below)
3. Access to folder and save the key on URL

1.2 - Google Sheets

1. Create a new sheets
2. Go to the bottom left and change the sheet name to everything you want or just leave it to default
3. Create a table with two columns (image - image_id)

Now we have finished setting up all the things, hope so 😂. We will start to code in the next section

2. Coding time

Like above, I will separate it into two small sections, one for Google Sheets and one for website 😁

2.1 Google Sheets

Go to the toolbars of the sheets you have created above and choose Extensions > Apps Script. The editor will show in a new tab, create a new script like image

Alright, we will go into api script first

2.1.1 API

This script will open an API gateway for your sheet with GET method, so your website can access and get the data
P/s: you must change SHEET_NAME to your value

/**
 * Retrieve data with GET method
 */
function doGet() {
  const sheetName = 'SHEET_NAME'; // Sheet name 

  var jsonArray = [];
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); 
  var data = spreadsheet.getDataRange().getValues();
  // Check if data is empty or doesn't contain enough rows for headers and at least one data row
  if (!data || data.length < 2) {
    // Return an empty array or a meaningful message as needed
    return 'No data'; // or return 'No data available';
  }
  var headers = data[0];
  for (var i = 1, length = data.length; i < length; i++) {
    var row = data[i];
    var record = {};

    for (var j = 0; j < row.length; j++) {
      record[headers[j]] = row[j];
    }

    jsonArray.push(record);
  }
  return ContentService.createTextOutput(JSON.stringify(jsonArray)).setMimeType(ContentService.MimeType.JSON); 
}

2.1.2 upload-image

We need to set up the last thing, is install libraries. I have found this repo for support process uploads images to Drive. Go to section libraries, click + and paste the key below to install

108j6x_ZX544wEhGkgddFYM6Ie09edDqXaFwnW3RVFQCLHw_mEueqUHTW

After install, you can follow the code below to develop. In here, you must change two things KEY_DRIVE &SHEET_NAME to your value 😉

/**
 * Create a custom menu item.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp, SlidesApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Upload Image', 'triggerUpload') // Add event listener for item
      .addToUi();
}

/**
 * Handle event click
 */
function triggerUpload() {
 const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 const currentCell = sheet.getCurrentCell();
 const nextCell = sheet.getRange(currentCell.getRowIndex(), currentCell.getColumnIndex() + 1);
 const urlImage = uploadImage(currentCell.getA1Notation(), currentCell.getRowIndex());
 nextCell.setValue(urlImage);
}

/**
 * Handle upload process
 * @param {string} cell - cell of image.
 */
function uploadImage(cell) {
  const destinationId = 'KEY_DRIVE'; // Key of public folder in Drive 
  const sheetName = 'SHEET_NAME'; // Sheet name you change above

  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const obj = DocsServiceApp.openBySpreadsheetId(spreadsheetId).getSheetByName(sheetName).getImages();
  const blobs = obj.filter(({range, image}) => range.a1Notation == cell && image.innerCell);
  const img = blobs[0].image.blob;
  const saveTo = DriveApp.getFolderById(destinationId) ;
  const uuid = Utilities.getUuid();
  var file = saveTo.createFile(img.setName(`${uuid}.png`));
  file.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);

  return file.getId();
}

After that, you need to deploy your app script before use.

2.1.3 Deploy

1. Click File > Save, and give your project a name
2. At the top right, click on Deploy > New Deployment.
3. Click on Select type and choose Web app. Under Execute as, choose run as your account. And under Who has access, choose Anyone
4. Click Deploy.

It will ask you some authorize step after that. When deploy successfully, it will open the dialog and you will copy the URL of the web app

And here is a demo for Google Sheets

Website

You can use fetch to get data, remember replace WEB_APP_URL to your web app value above

/**
 * Get repository info.
 */
function fetch_repository(){
  const url = "WEB_APP_URL"

  // Get data from API
  const response = await fetch(url).then((data) => data.json());
  return response;
}

That is the way we setup tinyCMS to store GitHub project. But do we miss something...

YESSSS, the thumbnail 😳. The main reason I do all those things is because I want my project have a thumbnail.

So how we can do it? image_id 😎. We can access the image on your drive by format below

https://lh3.googleusercontent.com/d/${image_id}

Base on that, I can do a function to get the image URL like

/**
 * Get thumbnail URL.
 * @param {string} id - image_id in sheet. 
 */
function get_thumbnail(image_id){
  return `https://lh3.googleusercontent.com/d/${image_id}`;
}

Conclusion

I just show you the way I store and display my project by combining Google Sheets + Google Drive. It may not be the best way but it is the positive way I can think. If you have any idea or comment, please let me know 😁

Happy Coding & Learning!