App script google sheet api

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

Nội dung chính Show

How do I use API in Google App script?
How do I connect an app script to a Google Sheet?
Is Google Apps Script an API?
Can you run scripts on Google Sheets app?

	/* Route
	* All Request with Method Get will be proces here
	*/
	function doGet(req) {
	var action = req.parameter.action;

	var db = SpreadsheetApp.openById("YOUR SPREADSHEET ID");

	// Don't forget to change your Sheet Name by default is 'Sheet1'
	var sheetUsers = db.getSheetByName("Users");

	switch(action) {
	case "read":
	return doRead(req, sheetUsers);
	break;
	case "insert":
	return doInsert(req, sheetUsers);
	break;
	case "update":
	return doUpdate(req, sheetUsers);
	break;
	case "delete":
	return doDelete(req, sheetUsers);
	break;
	default:
	return response().json({
	status: false,
	message: 'silent!'
	});
	}
	}

	/* Read
	* request for all Data
	*
	* @request-parameter \| action
	* @example-request \| ?action=read
	*/
	function doRead(request, sheetObject)
	{
	var data = {};

	data.records = _readData(sheetObject);

	return response().json(data);

	}

	/* Insert
	*
	*/
	function doInsert(req, sheet) {
	var id = req.parameter.id;
	var username = req.parameter.username;
	var email = req.parameter.email;
	// all data your needed

	var flag = 1;
	var Row = sheet.getLastRow();
	for (var i = 1; i <= Row; i++) {
	/* getRange(i, 2)
	* i \| is a row index
	* 1 \| is a id column index ('id')
	*/
	var idTemp = sheet.getRange(i, 1).getValue();
	if (idTemp == id) {
	flag = 0;
	var result = "Sorry bratha, id already exist";
	}
	}

	// add new row with recieved parameter from client
	if (flag == 1) {
	var timestamp = Date.now();
	var currentTime = new Date().toLocaleString(); // Full Datetime
	var rowData = sheet.appendRow([
	id,
	username,
	email,
	timestamp,
	currentTime
	]);
	var result = "Insertion successful";
	}

	return response().json({
	result: result
	});
	}

	/* Update
	* request for Update
	*
	* @request-parameter \| id, data, action
	* @example-request \| ?action=update&data={"email":"", "username":"nyancodeid"}
	*/
	function doUpdate(req, sheet)
	{
	var id = req.parameter.id;
	var updates = JSON.parse(req.parameter.data);

	var lr = sheet.getLastRow();

	var headers = _getHeaderRow(sheet);
	var updatesHeader = Object.keys(updates);

	// Looping for row
	for (var row = 1; row <= lr; row++) {
	// Looping for available header / column
	for (var i = 0; i <= (headers.length - 1); i++) {
	var header = headers[i];
	// Looping for column need to updated
	for (var update in updatesHeader) {
	if (updatesHeader[update] == header) {
	// Get ID for every row
	var rid = sheet.getRange(row, 1).getValue();

	if (rid == id) {
	// Lets Update
	sheet.getRange(row, i + 1).setValue(updates[updatesHeader[update]]);
	}
	}
	}
	}
	}


	// Output
	return response().json({
	status: true,
	message: "Update successfully"
	});
	}


	/* Delete
	*
	*/
	function doDelete(req, sheet) {
	var id = req.parameter.id;
	var flag = 0;

	var Row = sheet.getLastRow();
	for (var i = 1; i <= Row; i++) {
	var idTemp = sheet.getRange(i, 1).getValue();
	if (idTemp == id) {
	sheet.deleteRow(i);

	var result = "deleted successfully";
	flag = 1;
	}

	}

	if (flag == 0) {
	return response().json({
	status: false,
	message: "ID not found"
	});
	}

	return response().json({
	status: true,
	message: result
	});
	}


	/* Service
	*/
	function _readData(sheetObject, properties) {

	if (typeof properties == "undefined") {
	properties = _getHeaderRow(sheetObject);
	properties = properties.map(function (p) {
	return p.replace(/\s+/g, '_');
	});
	}

	var rows = _getDataRows(sheetObject),
	data = [];

	for (var r = 0, l = rows.length; r < l; r++) {
	var row = rows[r],
	record = {};

	for (var p in properties) {
	record[properties[p]] = row[p];
	}

	data.push(record);
	}

	return data;
	}
	function _getDataRows(sheetObject) {
	var sh = sheetObject;

	return sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
	}
	function _getHeaderRow(sheetObject) {
	var sh = sheetObject;

	return sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
	}
	function response() {
	return {
	json: function(data) {
	return ContentService
	.createTextOutput(JSON.stringify(data))
	.setMimeType(ContentService.MimeType.JSON);
	}
	}
	}

How do I use API in Google App script?

Example 1: Connecting Google Sheets to the Numbers API.

Step 1: Open a new Sheet. ... .

Step 2: Go to the Apps Script editor. ... .

Step 3: Name your project. ... .

Step 4: Add API example code. ... .

Step 5: Run your function. ... .

Step 6: Authorize your script. ... .

Step 7: View the logs. ... .

Step 8: Add data to Sheet..

How do I connect an app script to a Google Sheet?

Click Extensions > Apps Script to open the script editor, then copy the script text from the original spreadsheet and paste it into the script editor of another spreadsheet.

Is Google Apps Script an API?

The Google Apps Script API lets you programmatically create, modify, and deploy Apps Script projects—actions that otherwise require you to use the Apps Script editor. Your apps can use the API to manage your script projects, create and deploy new script versions, and monitor script executions.

Can you run scripts on Google Sheets app?

You can use Apps Script to add custom menus, dialogs, and sidebars to Google Sheets. It also lets you write custom functions for Sheets, as well as integrate Sheets with other Google services like Calendar, Drive, and Gmail.