Google Sheet + Google Drive = 🤯 - Part 2
Published: 2024-05-12 20:08:03

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

![sysdesign](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fw32uuajml6lutu97k1x.gif)

## 1. Env & Key
First of all, we need to set up environment and key to development. We would need two things <u>(Google Drive + Google Sheets)</u>, for sure 😁 . I will separate it into two sections

### 1.1 - Google Drive

1. Create a new folder 
2. Change <u>permission access</u> to public for editor (you can follow image below)
![Change permission](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uayyydpp4s5410bg4yn1.gif)
3. Access to folder and save the **key** on URL
![Key of folder](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wony0wcq745tkgxl4j60.png)


### 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 


![Coding time](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5pkgdz5pg0c9uhs7ajot.gif)

## 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

![new script](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xj5ns0b02maik8c0hz6r.png)

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 <u>SHEET_NAME</u> to your value
```js
/**
 * 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](https://github.com/tanaikech/DocsServiceApp) 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 <u>KEY_DRIVE </u> &<u>SHEET_NAME</u> to your value 😉
```js
/**
 * 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 

![web app url](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wa1weomfue0dsgfqq1wu.png)

And here is a demo for Google Sheets

![Sheet demo](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lyjkgr287ulhl5vjeeod.gif)

### Website
You can use <u>fetch</u> to get data, remember replace <u>WEB_APP_URL</u> to your web app value above 
```js
/**
 * 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...

![hmmm](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u3bgbi8jbw6ylfv2fi3g.gif)

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? <u>image_id</u> 😎. 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
```js
/**
 * 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!