To read data from an external API and populate it in a Google Sheet using Apps Script, you can use the UrlFetchApp class to make an HTTP request to the API and retrieve the data. Here’s an example code snippet:
function fetchDataFromAPI() { var apiUrl = 'https://api.example.com/data'; // Replace with the URL of the API endpoint var response = UrlFetchApp.fetch(apiUrl); var data = JSON.parse(response.getContentText()); var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getActiveSheet(); // Clear existing data in the sheet sheet.clearContents(); // Write data to the sheet var numRows = data.length; var numCols = Object.keys(data[0]).length; var range = sheet.getRange(1, 1, numRows, numCols); var values = []; for (var i = 0; i < data.length; i++) { var rowData = Object.values(data[i]); values.push(rowData); } range.setValues(values); }
In this code, the fetchDataFromAPI function fetches data from an external API and populates it in a Google Sheet. Replace 'https://api.example.com/data' with the actual URL of the API endpoint you want to retrieve data from.
The UrlFetchApp.fetch() method is used to make an HTTP GET request to the API and retrieve the response. The response is then parsed as JSON using JSON.parse().
The active spreadsheet is obtained using SpreadsheetApp.getActiveSpreadsheet(), and the active sheet is obtained using getActiveSheet().
Existing data in the sheet is cleared using the clearContents() method.
The number of rows and columns required in the sheet is determined based on the length of the data retrieved from the API.
A range is defined using getRange() to cover the required number of rows and columns.
The data retrieved from the API is formatted into a 2D array, values, which contains the rows and columns of data to be populated in the sheet.
Finally, the setValues() method is used to set the values of the range in the sheet with the retrieved data.
You can call the fetchDataFromAPI function from another function or set up triggers to execute it based on your needs. When executed, it will fetch data from the specified API and populate it in the Google Sheet.
Make sure to handle any authentication or API-specific requirements, such as including headers or API keys, as needed for successful API communication.