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.
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
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/