What is API

Brief Introduction to API.

To begin with, we should quickly refresh your memory on what is an API (abbreviation for Application Programming Interface). There are a wide range of definitions depending on the source however, we refer to Amazon Documentation for a brief summary below;

What is an API?

APIs are mechanisms that enable two software components to communicate with each other using a set of definitions and protocols. For example, the weather bureau’s software system contains daily weather data. The weather app on your phone “talks” to this system via APIs and shows you daily weather updates on your phone.

What does API stand for?

API stands for Application Programming Interface. In the context of APIs, the word Application refers to any software with a distinct function. Interface can be thought of as a contract of service between two applications. This contract defines how the two communicate with each other using requests and responses. Their API documentation contains information on how developers are to structure those requests and responses.

How do APIs work?

API architecture is usually explained in terms of client and server. The application sending the request is called the client, and the application sending the response is called the server. So in the weather example, the bureau’s weather database is the server, and the mobile app is the client. 

GOOGLE SHEETS API

Google also has comprehensive documentation which details everything you need to know about Google Sheets API. 

The Google Sheets API lets you read, write, and format Google Sheets data with your preferred programming language, including Java, JavaScript, and Python.

As part of its business suite for developers…,

The Google Sheets API is a RESTful interface that lets you read and modify a spreadsheet’s data. The most common uses of this API include the following tasks:

• Create spreadsheets
• Read spreadsheet cell values
• Write spreadsheet cell values
• Update spreadsheet formatting
• Manage Connected Sheets

The above are sometimes referred to as API functions. Each has very different steps and processes as well as outcomes for the end user. 

Before we dive into the various steps, we should take note of key terminologies that should be familiar already to the average user of this program.

1. Spreadsheet – is a Microsoft Office/business electronic document in which data is arranged in the rows and columns of a grid and can be manipulated and used in calculations. Two main providers of this application are Microsoft (Microsoft Excel) and Google Sheets (Google Business Suite).
2. Workbook – a standalone file containing spreadsheets. You can have multiple named spreadsheets in a workbook. For e.g. a workbook file named ‘Global Human Resources’ with named spreadsheets ‘UK-based Staff’(spreadsheet 1), ‘USA-based Staff’(spreadsheet 2), ‘Retirees’(spreadsheet 3).. etc.,
3. Cells –  An individual field of text or data within a spreadsheet. Cells are arranged in rows and columns, and can be grouped as a range of cells. A Cell Data resource represents each cell. Rows and column coordinates identify the cells. E.g. Cell A1 is the first cell in a spreadsheet with first column A. 
4. Rows – A tabular component of a spreadsheet in horizontal alignment. There are approximately 1 million rows in modern spreadsheets today.
5. Column – A tabular component of a spreadsheet in vertical alignment. 
6. Named range – A defined cell or range of cells with a custom name to simplify references throughout an application. A Filter-View resource represents a named range.
7. Protected range – A defined cell or range of cells that cannot be modified. A Protected Range resource represents a protected range.

Our Google API can be built with and Integrate with:

• Google Sheets API with Python
• Google Sheets API with Javascript
• Google Sheets API with PHP

How to use the SEARCH function in Google Sheets

Google Sheet is a great tool that allows you to organize data with numbers and text strings.

When working with lots of text in a spreadsheet, there may be situations where you need to obtain a specific word, letter, or number from a longer piece of text.

You can do that with the help of the SEARCH function in Google Sheets. It works by obtaining the position of a substring within a text string.

Do not confuse this with the other function – FIND which also returns the position of a substring within a string. 

The main difference is that the FIND function is case-sensitive, while SEARCH is not. 

So, if you’re working with data where the capitalization of the text does not matter, or you want to search for uppercase/lowercase text, then the SEARCH Google Sheets function is for you!

Syntax

=SEARCH(search_for, text_to_search, [starting_at])

• search_for – is the substring that you need to look for within the text_to_search.
• text_to_search – is the main text string within which you look for the first occurrence of the search_for substring.
• starting_at – [ OPTIONAL – 1 by default ] – is the position within the text_to_search from which the function starts looking for the search_for substring.

Further Explanation

As you can see from the syntax, this function needs both the string and substring. But the third parameter, [starting_at], is optional. 

You only need to use this if there are multiple occurrences of the same substring.

Below are a few examples of the SEARCH function.

Row 2

Here we are looking for “Hello”, which is in the very first position. 

Therefore, the function returns 1 as the output when we input the formula =Search(“Hello”,”Hello, there!”). 

As you can see from this example, the SEARCH function identifies the position where the substring starts: the word “Hello” occupies the first 5 characters in the text, but the function reveals where it starts – the very first character in the string.

Row 3

The second function finds the comma (,) in the third position of the main string. The formula here uses cell referencing instead of typing the text directly: =Search(B3,A3).

Row 4

The third function is quite interesting. Here we are looking for an empty space within the main string. However, instead of leaving out the third optional parameter, we chose to enter 10 as the starting_at parameter. 

Notice, the function ignored the first two occurrences of the space character (at 5 and 9) and returned the next immediate occurrence after the 10th position. The formula we input here is =Search(” “,A4,10).

Row 5

The fourth example illustrates the capability of the SEARCH function to ignore the case (upper or lower) of the text. This is the difference between this function and the FIND function as mentioned earlier.

Row 6

The last example shows that even if the main text is alpha-numeric, the function still works.

If the substring does not occur at all in the text, what happens? The function returns an error, as illustrated below:

Hopefully this summarizes the SEARCH function in Google Sheets. Now let’s look at executing a similar function but this time with the Google Sheet API. 

How to search with Google Sheets API

To search for cell contents in a Google Sheet, use the Read/Get method. Google Sheets API provides a spreadsheet.values collection to enable reading or writing values.

Depending on how much data you are searching for, key methods available include the following.

batchGet	Returns one or more ranges of values from a spreadsheet.
batchGetByDataFilter	Returns one or more ranges of values that match the specified data filters.
get	Returns a range of values from a spreadsheet.

Further details of the above methods can be found on Google’s developer documentation. Screenshot below.

Practice with real-life Google Sheets API setup

We have a Google Sheet file with records of ships involved in marine accidents over the last decade.

We would use Google Sheet API configured by NoCodeAPI to search and return records from rows and columns in this file.

Notice that the Google Sheet contains multiple named spreadsheets 

(VesselsDryDockDates, ShipInspections, Vessel_Email_DB, ShipCasualty, ShipSalesPurchaseDemolitions, MaritimeCompanies, etc.,)

Our focus is on searching for vessel records from the ShipCasualty spreadsheet.

Using a customized solution as provided by NoCodeAPI, we will assume you already have your API key and other settings already provisioned. 

Each dataset has its own unique URL. 

API key is per subscriber and can be used to access all the datasets which are included in your subscription.

Request

Method	URL ENDPOINT
GET	https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf

API_KEY

API_KEY must be sent with all client requests. The API_KEY helps the server to validate the request source. 

It is important that you keep this secure and avoid sharing with non-subscribers! We monitor usage and would reserve a right to limit the number of API calls you can make if we detect unfair usage.

Your unique API Key is ‘ENTER_YOUR_API_KEY_HERE’

Parameters

1. Get Ship Casualty Data

Make GET request on this endpoint to get rows with required parameters (if any required).

Base Endpoint excluding API_KEY – 

https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf

To get all casualty records on our database, use below URL – 

https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’

Adding Request Parameters for Ship Casualty Dataset:

Object	Description
tabId	[Required] Your tab Id is ShipCasualty Example below.https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’
row_id	[Optional] If you want one or more row(s) e.g., row number 10 would be –https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&row_id=10
perPage	[Optional] Total number of records you want in one request. For ShipCasualty, the default is 100,000 records per page. Note, for search results that would exceed 100,000 records, you must include page number e.g., 1/2/3/4/5/ etc.,Example below,https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100000&page=1
page	[Optional] Page number (e.g., 1 – 10). A maximum of 100,000 records can be displayed in 1 page. Example same as in above section,https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100000&page=1
filterBy	[Optional] key to filter result by any of the column headers. e.g., to filter by casualtytype column where type of casualty is ‘collision’, see URL below. Remember to add filtertype (equal, contain, greater – only for integer, less – only for integer) – https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100000&page=1&filterBy=CasualtyType&filterType=contain&filterValue=collision
filterValue	[Optional] text value to filter the rows. As in the above section, filter value can be anything. Example below filters the list where casualty type equal ‘fire’ –https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100,000&page=1&filterBy=CasualtyType&filterType=contain&filterValue=fireyou can now also filter by multiple values. Examples below.filter the list where casualty type is either one or, all the following: fire,collision,medical emergency,groundingPlease note, you must separate each filter value with a comma (,) and there should be no spacing after each comma. Spacing can only be handled if the complete filter value does inherit spacing. For example, ‘Torm Louise’ is a vessel name and therefore inherits a space so this should be fine.More examples with no spacing after each filter value. To return results where CasualtyType equal or contain (fire, or collision, or sank)URL should contain, filtervalue=fire,collision,sank https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100000&page=1&filterBy=CasualtyType&filterType=contain&filterValue=fire,collision,sankTo return results where VesselName equal or contain (KMP NUSA PENIDA, or LOWLANDS MIMOSA, or PUNTA MAYOR) URL should contain, filtervalue= KMP NUSA PENIDA,LOWLANDS MIMOSA,PUNTA MAYOR https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100000&page=1&filterBy=VesselName&filterType=contain&filterValue=KMP%20NUSA%20PENIDA,LOWLANDS%20MIMOSA,PUNTA%20MAYORTo filter the list where IMONumber is either one or, all the following: 88591847902726851359682074109311610850007082074109234989URL should contain, filtervalue=8859184,7902726,8513596,8207410,9311610,8500070,8207410,9234989https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100000&page=1&filterBy=IMONumber&filterType=equal&filterValue=8859184,7902726,8513596,8207410,9311610,8500070,8207410,9234989The following fields are also available for bulk/batch filtering on ShipCasualty data: IMONumber, VesselName, CasualtyTypeDO NOT USE spacing when separating your filter value like shown below.filtervalue=fire, collision, sankfiltervalue = KMP NUSA PENIDA, LOWLANDS MIMOSA, PUNTA MAYOR
filterType	[Optional] How you want to filter: default is equal. Valid values are equal, contain, greater – only for integer, less – only for integerAssuming you want to search for casualties where the vessel name is ‘MAERSK XXXX’, you can use equal (if you know the full name of the vessel) or contain (if you know only part of the vessel name) as filter type.E.g., to return all records which contain ‘Maersk’ in vessel name – https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100,000&page=1&filterBy=VesselName&filterType=contain&filterValue=maerskAnother E.g., to return all records where vessel name equal ‘Mary Maersk’ – https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100,000&page=1&filterBy=VesselName&filterType=equal&filterValue=MARY%20MAERSK

filterBy	[Optional] key to filter result by any of the column headers. e.g., to filter by casualtytype column where type of casualty is ‘collision’, see URL below. Remember to add filtertype (equal, contain, greater – only for integer, less – only for integer) – https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100000&page=1&filterBy=CasualtyType&filterType=contain&filterValue=collision
filterValue	[Optional] text value to filter the rows. As in the above section, filter value can be anything. Example below filters the list where casualty type equal ‘fire’ –https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100,000&page=1&filterBy=CasualtyType&filterType=contain&filterValue=fireyou can now also filter by multiple values. Examples below.filter the list where casualty type is either one or, all the following: fire,collision,medical emergency,groundingPlease note, you must separate each filter value with a comma (,) and there should be no spacing after each comma. Spacing can only be handled if the complete filter value does inherit spacing. For example, ‘Torm Louise’ is a vessel name and therefore inherits a space so this should be fine.More examples with no spacing after each filter value. To return results where CasualtyType equal or contain (fire, or collision, or sank)URL should contain, filtervalue=fire,collision,sank https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100000&page=1&filterBy=CasualtyType&filterType=contain&filterValue=fire,collision,sankTo return results where VesselName equal or contain (KMP NUSA PENIDA, or LOWLANDS MIMOSA, or PUNTA MAYOR)URL should contain, filtervalue= KMP NUSA PENIDA,LOWLANDS MIMOSA,PUNTA MAYOR https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100000&page=1&filterBy=VesselName&filterType=contain&filterValue=KMP%20NUSA%20PENIDA,LOWLANDS%20MIMOSA,PUNTA%20MAYORTo filter the list where IMONumber is either one or, all the following: 88591847902726851359682074109311610850007082074109234989URL should contain, filtervalue=8859184,7902726,8513596,8207410,9311610,8500070,8207410,9234989https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100000&page=1&filterBy=IMONumber&filterType=equal&filterValue=8859184,7902726,8513596,8207410,9311610,8500070,8207410,9234989The following fields are also available for bulk/batch filtering on ShipCasualty data: IMONumber, VesselName, CasualtyTypeDO NOT USE spacing when separating your filter value like shown below.filtervalue=fire, collision, sankfiltervalue= KMP NUSA PENIDA, LOWLANDS MIMOSA, PUNTA MAYOR
filterType	[Optional] How you want to filter: default is equal. Valid values are equal, contain, greater – only for integer, less – only for integerAssuming you want to search for casualties where the vessel name is ‘MAERSK XXXX’, you can use equal (if you know the full name of the vessel) or contain (if you know only part of the vessel name) as filter type.E.g., to return all records which contain ‘Maersk’ in vessel name – https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100,000&page=1&filterBy=VesselName&filterType=contain&filterValue=maerskAnother E.g., to return all records where vessel name equal ‘Mary Maersk’ – https://v1.nocodeapi.com/limpopo/google_sheets/MzqKOLBtSHMDGjAf?tabId=ShipCasualty&api_key=‘ENTER_YOUR_API_KEY_HERE’&perPage=100,000&page=1&filterBy=VesselName&filterType=equal&filterValue=MARY%20MAERSK

Sample API Output in JSON compressed format below

Conventions

• Client – Client application.
• Status – HTTP status code of response.
• All the possible responses are listed under ‘Responses’ for each method. Only one of them is issued per request server.
• All responses are in JSON format.
• All request parameters are mandatory unless explicitly marked as [Optional]

Status Codes

All status codes are standard HTTP status codes. The below ones are used in this API.

2XX – Success of some kind

4XX – Error occurred in client’s part5XX – Error occurred in server’s part