MUI Autocomplete using Django Rest API and Google Spreadsheet API

I made this task(this is not project) for learning purpose where I converted Google Spreadsheed to REST API, then converted data formation using python django and after that I used it on React MUI autocomplete.

Tutorial

---

Google Spreadsheet Link

Which code I have use at apps script of Google spreadsheet:

Apps Script

If you use below code then your data will show

function doGet(req) {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName('q3');
  var values = sheet.getDataRange().getValues();


  var output = [];
  for(var i = 0; i< values.length; i++) {
    var row = {};
    row['Item ID '] = values[i][0];
    row['Item'] = values[i][1];
    row['tag 1'] = values[i][2];
    row['tag 2'] = values[i][3];
    row['tag 3'] = values[i][4];

    output.push(row);
  }

  return ContentService.createTextOutput(JSON.stringify(output)).setMimeType(ContentService.MimeType.JSON);
}

output

[
  {
    "Item ID ": "Item ID",
    "Item": "Item",
    "tag 1": "tag 1",
    "tag 2": "tag 2",
    "tag 3": "tag 3"
  },
  {
    "Item ID ": 30003,
    "Item": "Thesis",
    "tag 1": 101,
    "tag 2": 103,
    "tag 3": ""
  },
  {
    "Item ID ": 30004,
    "Item": "Meditation",
    "tag 1": 110,
    "tag 2": 101,
    "tag 3": 111
  },
  {
    "Item ID ": 30005,
    "Item": "Helping",
    "tag 1": 403,
    "tag 2": 203,
    "tag 3": 120
  },
  {
    "Item ID ": 200669,
    "Item": "YouTube",
    "tag 1": 542,
    "tag 2": 87,
    "tag 3": ""
  }
]

here is the problem is spreadheet all columns has not values. For this problem I used below code.

function doGet(req) {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName('q3');
  var values = sheet.getDataRange().getValues();

  var studentQ3 = values.map(function(row){
    var formattedRow = [];
    for( var i =0; i<row.length; i++){
      var cellValue = row[i].toString().trim();
      if (cellValue !== ""){
        formattedRow.push(cellValue);
      }
    }
    return formattedRow;
  });

  return ContentService.createTextOutput(JSON.stringify(studentQ3)).setMimeType(ContentService.MimeType.JSON);
}

Output:

[
  [
    "Item ID",
    "Item",
    "tag 1",
    "tag 2",
    "tag 3"
  ],
  [
    "1001",
    "Malaysia",
    "101",
    "102",
    "103"
  ],
  [
    "1002",
    "Turkey",
    "201",
    "202",
    "203"
  ],
  [
    "1003",
    "Bangladesh",
    "301",
    "302",
    "303"
  ]
]

---

For frontend setup

$ git clone https://github.com/mushfiqur-rahman/google_spreadhseetapi_mui_autocomplete.git

$ cd frontend

$ frontent> npm install & npm run dev

Now open your browser & got to the url

 http://127.0.0.1:5173/

---

For Backend setup

$ cd backend

$ backend > py -m venv venv

$ backend > venv\scripts\activate

$ backend > venv\scripts\activate

(venv) PS D:\backend> python -m pip install -r requirements.txt

(venv) PS D:\backend> py manage.py migrate

(venv) PS D:\backend> py manage.py runserver

Student Needs API URL

http://127.0.0.1:8000/q1/
http://127.0.0.1:8000/q2/
http://127.0.0.1:8000/q3/

Post URL

http://127.0.0.1:8000/api/services/

Material UI Autocomplete