Conectar API a Google Sheets te permite automatizar el flujo de datos desde tu app o fuente y olvidarte de tener que exportar/importar manualmente los datos que necesitas. Para ello, basta con escribir unas pocas líneas de código y disfrutar de la automatización. Ahora bien, ¿qué ocurre con los usuarios no expertos en tecnología que no tienen conocimientos de codificación? También disponen de una solución que les permite importar datos de API a Google Sheets con unos pocos clics. Sigue leyendo para descubrir ambas opciones que pueden hacerte la vida más fácil.
Opciones para conectar REST API a Google Sheets
Existen dos formas habituales de añadir la API JSON a Google Sheets: con código y sin código.
Coupler.io (sin código): Coupler.io es una herramienta de automatización de informes que conecta fácilmente las API REST con Google Sheets sin necesidad de codificación. Los usuarios pueden programar importaciones regulares utilizando su integración JSON desde cualquier aplicación a diferentes destinos. Para configurar esta conexión, necesitarás la URL de la API REST de la aplicación y conocimientos básicos de API.
Scripts personalizados (código): Si sabes programar, puedes escribir secuencias de comandos personalizadas para conectar las API de REST a Google Sheets. Utilizarás Google Apps Script, Python u otros lenguajes para importar y manipular datos. Estos scripts también ayudan a integrarse con las herramientas y los servicios de Google Workspace. Aunque requiere tiempo y conocimientos de programación, es perfecto para personalizaciones avanzadas.
Cómo importar API a Google Sheets sin codificar
Paso 1: Recopilar datos
Haz clic en Proceed en el siguiente formulario. El origen (JSON) y el destino (Google Sheets) están preseleccionados.
Se te ofrecerá empezar a utilizar Coupler.io de forma gratuita. Después de eso, configura la conexión API de la siguiente manera:
- Inserte la JSON URL: esta es la URL de la API + el endpoint donde se envían las peticiones HTTP. El endpoint es la URL postfix que difiere dependiendo del tipo de datos cargados desde la API. Puedes encontrar la URL JSON en la documentación de la API RESTful de tu fuente de datos. Por ejemplo, la URL JSON utilizada para obtener una lista de documentos de Coda es la siguiente:
https://coda.io/apis/v1/docs
- Método HTTP: puedes elegir un método HTTP para realizar una solicitud basándote en la documentación de tu plataforma de origen de datos. GET es el método por defecto.
- Request headers: puedes aplicar cabeceras HTTP específicas a tu solicitud. Por ejemplo, la cabecera Authorization te permite especificar las credenciales necesarias para realizar una solicitud HTTP. Este es el aspecto que tiene para Coda:
Authorization: Bearer {API-token}
- URL query parameters: puedes utilizar parámetros de filtro si están asociados a la URL JSON de la API. Por ejemplo, esta es la cadena de consulta URL para filtrar la lista de conversaciones por buzón y estado en la API de Help Scout:
mailbox: {mailbox_id}
status: open
- Columns: puedes especificar los campos (columnas) que se importarán a la hoja de cálculo. Por ejemplo:
customer_firstname, customer_lastname, customer_email, status
- Path: puedes seleccionar objetos anidados de la respuesta JSON. En la mayoría de los casos, esto te permite excluir cualquier información innecesaria en los datos exportados desde la API. Por ejemplo, utilizando el siguiente parámetro Path, los datos de Help Scout se colocarán en varias filas.
_embedded.mailboxes
Nota: Puedes encontrar información sobre los parámetros utilizados en esos campos en la documentación de la API de tu aplicación. Presta atención a la información sobre la autenticación de la API y a los detalles de los puntos finales de la API: suelen encontrarse en secciones separadas.
Paso 2: Organizar y transformar los datos
Aquí verás la vista previa de los datos del archivo JSON. También puedes transformar estos datos utilizando las siguientes funciones.
- Reorganizar, cambiar el nombre, ocultar y desocultar las columnas según sea necesario
- Ordenar y filtrar los datos
- Añadir nuevas columnas mediante fórmulas personalizadas
Paso 3: Cargar datos y programar la transferencia
Cuando los datos estén listos para ser exportados, vamos a configurar Google Sheets como destino. Conecta la cuenta de Google Sheets, selecciona la hoja de cálculo y el nombre de la hoja.
Opcionalmente, puedes especificar el rango de celdas para la importación de datos. Dejémoslo por defecto en A1.
Crea un programa de actualización de datos para exportar periódicamente los datos de JSON a Google Sheets de forma automática. Elige el intervalo, los días de la semana, las preferencias horarias y la zona horaria que desees.
Una vez que hagas clic en Save and run the importer, verás los datos en la hoja de cálculo de Google.
También puedes instalar el complemento Coupler.io para Google Sheets desde Google Workspace Marketplace y, a continuación, realizar la configuración directamente en la hoja de cálculo.
Cómo utilizar una API externa en Google Sheets mediante código
Arriba, hemos añadido la API JSON a Google Sheets sin una línea de código, ¿verdad? Ahora, echemos un vistazo al lado oscuro de la luna. Exploraremos la solución más obvia basada en el Script de Google Apps en Google Sheets.
¿Cómo extraer datos de la API en Google Sheets con Apps Script?
La idea de este enfoque es crear una función personalizada de Google Sheets que obtenga y convierta los datos JSON de forma manual o automática.
Abre tu documento de Google Sheets y ve a Extensions => Apps Script.
Añade el siguiente código creado por Brad Jasper y Trevor Lohrbeer al Editor de Script, asigna un nombre a tu proyecto y haz clic en Save:
function ImportJSON(url, query, parseOptions) {
return ImportJSONAdvanced(url, null, query, parseOptions, includeXPath_, defaultTransform_);
}
/**
* Importa un feed JSON a través de una petición POST y devuelve los resultados para ser insertados en una hoja de cálculo de Google. El feed JSON es
* aplanado para crear una matriz bidimensional. La primera fila contiene las cabeceras, y cada cabecera de columna indica la ruta a
* esos datos en el feed JSON. Las filas restantes contienen los datos.
*
* Para recuperar el JSON, se envía una solicitud POST a la URL y la carga útil se pasa como contenido de la solicitud utilizando el parámetro content
* tipo "application/x-www-form-urlencoded". Si las fetchOptions definen un valor para "method", "payload" o "contentType", éstos
* tendrán prioridad. Por ejemplo, los usuarios avanzados pueden usar esto para hacer que esta función pase XML como la carga útil usando un GET
* y un tipo de contenido "application/xml; charset=utf-8". Para obtener más información sobre las opciones de obtención disponibles, consulte
* https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app . En este momento no se admite la opción "cabeceras".
*
* Por defecto, los datos devueltos se transforman para que se parezcan más a una importación de datos normal. Específicamente:
*
* - Los datos de los elementos JSON padre se heredan a sus elementos hijo, por lo que las filas que representan elementos hijo contienen los valores
* de las filas que representan sus elementos padre.
* - Los valores superiores a 256 caracteres se truncan.
* - Las barras inclinadas de los encabezados se convierten en espacios, se eliminan los prefijos comunes y el texto resultante se convierte a mayúsculas y minúsculas.
*
* Para cambiar este comportamiento, introduzca uno de estos valores en el parámetro options:
*
* noInherit: No heredar valores de elementos padre
* noTruncate: No truncar valores
* rawHeaders: No adornar las cabeceras
* noHeaders: No incluir cabeceras, sólo los datos
* allHeaders: Incluye todas las cabeceras del parámetro de consulta en el orden en que se enumeran
* debugLocation: Anteponer a cada valor la fila y columna a la que pertenece
*
* Por ejemplo:
*
* =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", "user=bob&apikey=xxxx",
* "validateHttpsCertificates=false", "/feed/entry/title,/feed/entry/content", "noInherit,noTruncate,rawHeaders")
*
* @param {url} la URL a un feed JSON público
* @param {payload} el contenido a pasar con la petición POST; normalmente una lista de parámetros codificada en URL y separada por ampersands.
* @param {fetchOptions} una lista separada por comas de las opciones utilizadas para recuperar la fuente JSON de la URL
* @param {query} una lista separada por comas de rutas a importar. Se importará cualquier ruta que empiece por una de estas rutas.
* @param {parseOptions} una lista separada por comas de opciones que modifican el procesamiento de los datos
* @customfunction
*
* @devuelve un array bidimensional que contiene los datos, con la primera fila conteniendo las cabeceras
**/
function ImportJSONViaPost(url, payload, fetchOptions, query, parseOptions) {
var postOptions = parseToObject_(fetchOptions);
if (postOptions["method"] == null) {
postOptions["method"] = "POST";
}
if (postOptions["payload"] == null) {
postOptions["payload"] = payload;
}
if (postOptions["contentType"] == null) {
postOptions["contentType"] = "application/x-www-form-urlencoded";
}
convertToBool_(postOptions, "validateHttpsCertificates");
convertToBool_(postOptions, "useIntranet");
convertToBool_(postOptions, "followRedirects");
convertToBool_(postOptions, "muteHttpExceptions");
return ImportJSONAdvanced(url, postOptions, query, parseOptions, includeXPath_, defaultTransform_);
}
/**
* Importa un texto JSON de una Hoja con nombre y devuelve los resultados para ser insertados en una Hoja de Cálculo de Google. La fuente JSON se aplana para crear
* una matriz bidimensional. La primera fila contiene las cabeceras, y cada cabecera de columna indica la ruta a esos datos en
* el feed JSON. Las filas restantes contienen los datos.
*
* Por defecto, los datos se transforman para que se parezcan más a una importación de datos normal. Específicamente:
*
* - Los datos de los elementos JSON padre se heredan a sus elementos hijo, por lo que las filas que representan elementos hijo contienen los
* valores
* - Los valores superiores a 256 caracteres se truncan.
* - Las barras inclinadas de los encabezados se convierten en espacios, se eliminan los prefijos comunes y el texto resultante se convierte a mayúsculas y minúsculas.
*
* Para cambiar este comportamiento, introduzca uno de estos valores en el parámetro options:
*
* noInherit: No heredar valores de elementos padre
* noTruncate: No truncar valores
* rawHeaders: No adornar las cabeceras
* noHeaders: No incluir cabeceras, sólo los datos
* allHeaders: Incluye todas las cabeceras del parámetro de consulta en el orden en que se enumeran
* debugLocation: Anteponer a cada valor la fila y columna a la que pertenece
*
* Por ejemplo:
*
* =ImportJSONFromSheet("Source", "/feed/entry/title,/feed/entry/content",
* "noInherit,noTruncate,rawHeaders")
*
* @param {sheetName} el nombre de la hoja que contiene el texto para el JSON
* @param {query} una lista separada por comas de rutas a importar. Se importará cualquier ruta que empiece por una de estas rutas.
* @param {options} una lista separada por comas de opciones que alteran el procesamiento de los datos
*
* @devuelve un array bidimensional que contiene los datos, con la primera fila conteniendo las cabeceras
* @customfunction
**/
function ImportJSONFromSheet(sheetName, query, options) {
var object = getDataFromNamedSheet_(sheetName);
return parseJSONObject_(object, query, options, includeXPath_, defaultTransform_);
}
/**
* Una versión avanzada de ImportJSON diseñada para ser extendida fácilmente por un script. Esta versión no puede ser llamada desde dentro de un
* Hoja de cálculo.
*
* Importa un feed JSON y devuelve los resultados para insertarlos en una hoja de cálculo de Google. El feed JSON se aplana para crear
* una matriz bidimensional. La primera fila contiene las cabeceras, y cada cabecera de columna indica la ruta a esos datos en
* el feed JSON. Las filas restantes contienen los datos.
*
* Las fetchOptions se pueden utilizar para cambiar la forma en que se recupera la fuente JSON. Por ejemplo, las opciones "method" y "payload" pueden ser
* configurado para pasar una petición POST con parámetros post. Para obtener más información sobre los parámetros disponibles, consulte
* https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app .
*
* Utilice las funciones de inclusión y transformación para determinar qué incluir en la importación y cómo transformar los datos una vez importados.
* Importado.
*
* Por ejemplo:
*
* ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json",
* new Object() { "method" : "post", "payload" : "user=bob&apikey=xxxx" },
* "/feed/entry",
* "",
* function (query, path) { return path.indexOf(query) == 0; },
* function (data, row, column) { data[row][column] = data[row][column].toString().substr(0, 100); } )
*
* En este ejemplo, la función de importación comprueba si la ruta a los datos que se importan comienza con la consulta. La transformación
* toma los datos y los trunca. Para versiones más robustas de estas funciones, consulte el código interno de esta biblioteca.
*
* @param {url} la URL a un feed JSON público
* @param {fetchOptions} un objeto cuyas propiedades son opciones utilizadas para recuperar el feed JSON de la URL
* @param {query} la consulta pasada a la función include
* @param {parseOptions} una lista separada por comas de opciones que pueden alterar el procesamiento de los datos
* @param {includeFunc} una función con la firma func(query, path, options) que devuelve true si el elemento de datos en la ruta dada
* debe incluirse o falso en caso contrario.
* @param {transformFunc} una función con la firma func(datos, fila, columna, opciones) donde datos es una matriz bidimensional de los datos
* y fila & columna son la fila y la columna actuales que se están procesando.
* Cualquier valor de retorno se ignora. Tenga en cuenta que la fila 0
* contiene las cabeceras de los datos, así que comprueba row==0 para procesar sólo las cabeceras.
*
* @devuelve un array bidimensional que contiene los datos, con la primera fila conteniendo las cabeceras
* @customfunction
**/
function ImportJSONAdvanced(url, fetchOptions, query, parseOptions, includeFunc, transformFunc) {
var jsondata = UrlFetchApp.fetch(url, fetchOptions);
var object = JSON.parse(jsondata.getContentText());
return parseJSONObject_(object, query, parseOptions, includeFunc, transformFunc);
}
/**
* Función de ayuda para autenticar con información de autenticación básica usando ImportJSONAdvanced
*
* Importa un feed JSON y devuelve los resultados para insertarlos en una hoja de cálculo de Google. El feed JSON se aplana para crear
* una matriz bidimensional. La primera fila contiene las cabeceras, y cada cabecera de columna indica la ruta a esos datos en
* el feed JSON. Las filas restantes contienen los datos.
*
* Las fetchOptions se pueden utilizar para cambiar la forma en que se recupera la fuente JSON. Por ejemplo, las opciones "method" y "payload" pueden ser
* establecido para pasar una petición POST con parámetros post. Para obtener más información sobre los parámetros disponibles, consulte
* https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app .
*
* Utilice las funciones de inclusión y transformación para determinar qué incluir en la importación y cómo transformar los datos una vez importados.
* Importado.
*
* @param {url} la URL a un feed JSON protegido por http basic auth
* @param {username} el nombre de usuario para la autenticación
* @param {password} la Contraseña para la autenticación
* @param {query} la consulta pasada a la función include (opcional)
* @param {parseOptions} una lista separada por comas de opciones que pueden alterar el procesamiento de los datos (opcional)
*
* @devuelve un array bidimensional que contiene los datos, con la primera fila conteniendo las cabeceras
* @customfunction
**/
function ImportJSONBasicAuth(url, username, password, query, parseOptions) {
var encodedAuthInformation = Utilities.base64Encode(username + ":" + password);
var header = {headers: {Authorization: "Basic " + encodedAuthInformation}};
return ImportJSONAdvanced(url, header, query, parseOptions, includeXPath_, defaultTransform_);
}
/**
* Codifica el valor dado para utilizarlo dentro de una URL.
*
* @param {value} el valor a codificar
*
* @return el valor codificado utilizando la codificación porcentual de URL
*/
function URLEncode(value) {
return encodeURIComponent(value.toString());
}
/**
* Añade un servicio oAuth utilizando el nombre dado y la lista de propiedades.
*
* @note Este método es un experimento para intentar averiguar cómo añadir un servicio oAuth sin tener que especificarlo en cada
* Llamada a ImportJSON. La idea era llamar a este método en la primera celda de una hoja de cálculo, y luego utilizar ImportJSON en otros
* células. Esto no funcionó, pero dejando esto aquí para la experimentación más adelante.
*
* La prueba que hice fue añadir lo siguiente en el A1:
*
* =AddOAuthService("twitter", "https://api.twitter.com/oauth/access_token",
* "https://api.twitter.com/oauth/request_token", "https://api.twitter.com/oauth/authorize",
* "<my consumer key>", "<my consumer secret>", "", "")
*
* Para obtener información sobre cómo obtener una clave de consumidor y un secreto para Twitter, consulta https://dev.twitter.com/docs/auth/using-oauth.
*
* Luego añadí lo siguiente en A2:
*
* =ImportJSONViaPost("https://api.twitter.com/1.1/statuses/user_timeline.json?screen_name=fastfedora&count=2", "",
* "oAuthServiceName=twitter,oAuthUseToken=always", "/", "")
*
* Recibí un error de que el "oAuthServiceName" no era un valor válido. [twl 18.abr.13]
*/
function AddOAuthService__(name, accessTokenUrl, requestTokenUrl, authorizationUrl, consumerKey, consumerSecret, method, paramLocation) {
var oAuthConfig = UrlFetchApp.addOAuthService(name);
if (accessTokenUrl != null && accessTokenUrl.length > 0) {
oAuthConfig.setAccessTokenUrl(accessTokenUrl);
}
if (requestTokenUrl != null && requestTokenUrl.length > 0) {
oAuthConfig.setRequestTokenUrl(requestTokenUrl);
}
if (authorizationUrl != null && authorizationUrl.length > 0) {
oAuthConfig.setAuthorizationUrl(authorizationUrl);
}
if (consumerKey != null && consumerKey.length > 0) {
oAuthConfig.setConsumerKey(consumerKey);
}
if (consumerSecret != null && consumerSecret.length > 0) {
oAuthConfig.setConsumerSecret(consumerSecret);
}
if (method != null && method.length > 0) {
oAuthConfig.setMethod(method);
}
if (paramLocation != null && paramLocation.length > 0) {
oAuthConfig.setParamLocation(paramLocation);
}
}
/**
* Analiza un objeto JSON y devuelve una matriz bidimensional que contiene los datos de ese objeto.
*/
function parseJSONObject_(object, query, options, includeFunc, transformFunc) {
var headers = new Array();
var data = new Array();
if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) {
query = query.toString().split(",");
}
// Rellenar las cabeceras para fijar su orden
if (hasOption_(options, "allHeaders") && Array.isArray(query))
{
for (var i = 0; i < query.length; i++)
{
headers[query[i]] = Object.keys(headers).length;
}
}
if (options) {
options = options.toString().split(",");
}
parseData_(headers, data, "", {rowIndex: 1}, object, query, options, includeFunc);
parseHeaders_(headers, data);
transformData_(data, options, transformFunc);
return hasOption_(options, "noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data;
}
/**
* Analiza los datos contenidos en el valor dado y los inserta en la matriz bidimensional de datos comenzando en el rowIndex.
* Si los datos se van a insertar en una nueva columna, se añade una nueva cabecera a la matriz de cabeceras. El valor puede ser un objeto,
* array o valor escalar.
*
* Si el valor es un objeto, se recorren sus propiedades y se devuelven a esta función con el nombre de cada una de ellas.
* que amplía la ruta. Por ejemplo, si el objeto contiene la propiedad "entry" y la ruta pasada fue "/feed",
* esta función se llama con el valor de la propiedad entry y la ruta "/feed/entry".
*
* Si el valor es una matriz que contiene otras matrices u objetos, cada elemento de la matriz se pasa a esta función con
* el rowIndex incrementado para cada elemento.
*
* Si el valor es una matriz que contiene sólo valores escalares, esos valores se unen y se insertan en la matriz de datos como
* un único valor.
*
* Si el valor es un escalar, el valor se inserta directamente en la matriz de datos.
*/
function parseData_(headers, data, path, state, value, query, options, includeFunc) {
var dataInserted = false;
if (Array.isArray(value) && isObjectArray_(value)) {
for (var i = 0; i < value.length; i++) {
if (parseData_(headers, data, path, state, value[i], query, options, includeFunc)) {
dataInserted = true;
if (data[state.rowIndex]) {
state.rowIndex++;
}
}
}
} else if (isObject_(value)) {
for (key in value) {
if (parseData_(headers, data, path + "/" + key, state, value[key], query, options, includeFunc)) {
dataInserted = true;
}
}
} else if (!includeFunc || includeFunc(query, path, options)) {
// Manejar matrices que contienen sólo valores escalares
if (Array.isArray(value)) {
value = value.join();
}
// Insertar nueva fila si aún no existe ninguna
if (!data[state.rowIndex]) {
data[state.rowIndex] = new Array();
}
// Añadir una nueva cabecera si no existe
if (!headers[path] && headers[path] != 0) {
headers[path] = Object.keys(headers).length;
}
// Insertar los datos
data[state.rowIndex][headers[path]] = value;
dataInserted = true;
}
return dataInserted;
}
/**
* Analiza la matriz de encabezados y la inserta en la primera fila de la matriz de datos.
*/
function parseHeaders_(headers, data) {
data[0] = new Array();
for (key in headers) {
data[0][headers[key]] = key;
}
}
/**
* Aplica la función de transformación para cada elemento de la matriz de datos, pasando por cada columna de cada fila.
*/
function transformData_(data, options, transformFunc) {
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[0].length; j++) {
transformFunc(data, i, j, options);
}
}
}
/**
* Devuelve true si el valor de prueba dado es un objeto; false en caso contrario.
*/
function isObject_(test) {
return Object.prototype.toString.call(test) === '[object Object]';
}
/**
* Devuelve true si el valor de prueba dado es un array que contiene al menos un objeto; false en caso contrario.
*/
function isObjectArray_(test) {
for (var i = 0; i < test.length; i++) {
if (isObject_(test[i])) {
return true;
}
}
return false;
}
/**
* Devuelve true si la consulta dada se aplica a la ruta dada.
*/
function includeXPath_(query, path, options) {
if (!query) {
return true;
} else if (Array.isArray(query)) {
for (var i = 0; i < query.length; i++) {
if (applyXPathRule_(query[i], path, options)) {
return true;
}
}
} else {
return applyXPathRule_(query, path, options);
}
return false;
};
/**
* Devuelve true si la regla se aplica a la ruta dada.
*/
function applyXPathRule_(rule, path, options) {
return path.indexOf(rule) == 0;
}
/**
* Por defecto, esta función transforma el valor en la fila y columna dadas para que se parezca más a una importación de datos normal. Específicamente:
*
* - Los datos de los elementos JSON padre se heredan a sus elementos hijo, por lo que las filas que representan elementos hijo contienen los valores
* de las filas que representan sus elementos padre.
* - Los valores superiores a 256 caracteres se truncan.
* - Los valores de la fila 0 (encabezados) se convierten en espacios, se eliminan los prefijos comunes y el texto resultante se convierte en título
* caso.
*
* Para cambiar este comportamiento, introduzca uno de estos valores en el parámetro options:
*
* noInherit: No heredar valores de elementos padre
* noTruncate: No truncar valores
* rawHeaders: No adornar las cabeceras
* debugLocation: Anteponer a cada valor la fila y columna a la que pertenece
*/
function defaultTransform_(data, row, column, options) {
if (data[row][column] == null) {
if (row < 2 || hasOption_(options, "noInherit")) {
data[row][column] = "";
} else {
data[row][column] = data[row-1][column];
}
}
if (!hasOption_(options, "rawHeaders") && row == 0) {
if (column == 0 && data[row].length > 1) {
removeCommonPrefixes_(data, row);
}
data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g, " "));
}
if (!hasOption_(options, "noTruncate") && data[row][column]) {
data[row][column] = data[row][column].toString().substr(0, 256);
}
if (hasOption_(options, "debugLocation")) {
data[row][column] = "[" + row + "," + column + "]" + data[row][column];
}
}
/**
* Si todos los valores de la fila comparten el mismo prefijo, elimínelo.
*/
function removeCommonPrefixes_(data, row) {
var matchIndex = data[row][0].length;
for (var i = 1; i < data[row].length; i++) {
matchIndex = findEqualityEndpoint_(data[row][i-1], data[row][i], matchIndex);
if (matchIndex == 0) {
return;
}
}
for (var i = 0; i < data[row].length; i++) {
data[row][i] = data[row][i].substring(matchIndex, data[row][i].length);
}
}
/**
* Localiza el índice donde los valores de las dos cadenas dejan de ser iguales, deteniéndose automáticamente en el índice stopAt.
*/
function findEqualityEndpoint_(string1, string2, stopAt) {
if (!string1 || !string2) {
return -1;
}
var maxEndpoint = Math.min(stopAt, string1.length, string2.length);
for (var i = 0; i < maxEndpoint; i++) {
if (string1.charAt(i) != string2.charAt(i)) {
return i;
}
}
return maxEndpoint;
}
/**
* Convierte el texto a mayúsculas y minúsculas.
*/
function toTitleCase_(text) {
if (text == null) {
return null;
}
return text.replace(/\w\S*/g, function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); });
}
/**
* Devuelve true si el conjunto de opciones dado contiene la opción dada.
*/
function hasOption_(options, option) {
return options && options.indexOf(option) >= 0;
}
/**
* Analiza la cadena dada en un objeto, recortando cualquier espacio inicial o final de las claves.
*/
function parseToObject_(text) {
var map = new Object();
var entries = (text != null && text.trim().length > 0) ? text.toString().split(",") : new Array();
for (var i = 0; i < entries.length; i++) {
addToMap_(map, entries[i]);
}
return map;
}
/**
* Analiza la entrada dada y la añade al mapa dado, recortando cualquier espacio inicial o final de la clave.
*/
function addToMap_(map, entry) {
var equalsIndex = entry.indexOf("=");
var key = (equalsIndex != -1) ? entry.substring(0, equalsIndex) : entry;
var value = (key.length + 1 < entry.length) ? entry.substring(key.length + 1) : "";
map[key.trim()] = value;
}
/**
* Devuelve el valor dado como un booleano.
*/
function toBool_(value) {
return value == null ? false : (value.toString().toLowerCase() == "true" ? true : false);
}
/**
* Convierte el valor de la clave dada en el mapa dado en un bool.
*/
function convertToBool_(map, key) {
if (map[key] != null) {
map[key] = toBool_(map[key]);
}
}
function getDataFromNamedSheet_(sheetName) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var source = ss.getSheetByName(sheetName);
var jsonRange = source.getRange(1,1,source.getLastRow());
var jsonValues = jsonRange.getValues();
var jsonText = "";
for (var row in jsonValues) {
for (var col in jsonValues[row]) {
jsonText +=jsonValues[row][col];
}
}
Logger.log(jsonText);
return JSON.parse(jsonText);
}
Este Apps Script acumula unas cuantas funciones para que puedas importar JSON desde API a Google Sheets:
ImportJSON()– para importar JSON desde una URL API.ImportJSONFromSheet()– para importar JSON desde una de las Hojas.ImportJSONViaPost()– para importar JSON desde una URL API utilizando parámetros POST.ImportJSONBasicAuth()– para importar JSON desde una URL de API con HTTP Basic Auth.ImportJSONAdvanced()– para importar JSON utilizando parámetros avanzados.
Más información sobre el script en el Github de Brad.
Estas funciones personalizadas funcionan del mismo modo que la mayoría de las funciones de Google Sheets. Por ejemplo, esta es la sintaxis de ImportJSON():
=ImportJSON(url, query, parseOptions)urles la URL de la API a un archivo JSONqueryes una lista separada por comas de rutas a importar (parámetro opcional)parseOptionses una lista separada por comas de opciones que modifican el procesamiento de los datos (parámetro opcional)
He aquí cómo funciona en acción. Hemos utilizado la función para importar los tipos de cambio actuales de la API de tipos de cambio:
=importjson("http://api.exchangeratesapi.io/v1/latest?access_key={your-access-key}")
ImportJSON() funciona para APIs JSON disponibles públicamente. Por lo tanto, si necesitas analizar datos JSON de una API que requiere un token de API para la autorización (Typeform, por ejemplo), la función fallará. Sin embargo, esto se puede arreglar de la siguiente manera.
Apps Script para subir JSON a Google Sheets usando un token API
Añade el siguiente fragmento de código al script en tu Editor de Script y guarda el proyecto
function ImportJSONAuth(url, api_token, query, parseOptions) {
var header = {headers: {
'Authorization': api_token,
'Accept': 'application/json'}};
return ImportJSONAdvanced(url, header, query, parseOptions, includeXPath_, defaultTransform_);
}
Crea una nueva función llamada ImportJSONAuth(), que añade la cabecera Authorization a la solicitud HTTP desde Google Sheets a la API de destino. Todo lo que necesitas hacer es llamar a la función desde la hoja de cálculo y especificar dos parámetros requeridos:
- URL de la API JSON
- Token de API en el formato: Bearer {your-API-token}
Mira cómo funciona:
=ImportJSONAuth("https://api.typeform.com/forms", "Bearer 3K**********************Krbe")
Echa un vistazo a cómo lo utilizamos para importar datos de GitHub a Google Sheets.
Conectar la API a Google Sheets de forma programada
En el Editor de Script, puedes configurar activadores temporales para ejecutar tus funciones personalizadas. Para ello, ve a Triggers:
Añade un disparador para automatizar la importación de datos desde la API a Google Sheets. Explicamos en detalle cómo hacerlo en el tutorial Cómo exportar Google Calendar a Google Sheets.
Qué es una API: un repaso para los no técnicos
API es el acrónimo de Application Programming Interface (interfaz de programación de aplicaciones). Aclaremos esto:
- Interfaz: método o mecanismo
- Programación de aplicaciones: interacción o comunicación entre dos aplicaciones o programas informáticos.
Así pues, una API es un mecanismo de comunicación entre una aplicación y otra. En nuestro caso, API to Google Sheets, utilizaremos API para conectar una app a Google Sheets.
Significado de la API REST
Ahora que está claro qué significa API, ¿qué pasa con API REST? REST es un conjunto de principios arquitectónicos que conforman la API. Su esencia es la siguiente:
- Cuando una aplicación cliente envía una solicitud a una aplicación servidor, obtiene una representación del estado del recurso. Esta información se entrega a través de HTTP en un formato específico. El formato más popular para representar información es JSON, que es independiente del lenguaje y legible por humanos al mismo tiempo.
En vista de ello, es posible que te encuentres con APIs que se llaman APIs JSON, APIs Web o incluso APIs HTTP. En la mayoría de los casos, todas significan lo mismo.
¿Cuál es la mejor forma de conectar datos de API a Google Sheets?
Hemos demostrado que se puede conectar la API a Google Sheets con unos pocos clics y un poco de investigación. Aunque no es superfácil, tampoco es demasiado difícil. Los scripts personalizados ofrecen flexibilidad y control sobre el manejo de los datos, pero requieren conocimientos de codificación, cuyo aprendizaje puede llevar mucho tiempo.
Por suerte, Coupler.io no requiere codificación para conectar la API a Google Sheets. Utiliza un conector JSON para extraer datos de la mayoría de las API JSON. Además, Coupler.io también admite integraciones directas desde más de 60 aplicaciones de origen a Google Sheets, Looker Studio, BigQuery, etc.
Aprende a conectar la API a Looker Studio.
Ambos métodos tienen sus ventajas, así que elige el que mejor se adapte a tus necesidades. ¡Buena suerte con tus datos!
Conectar API a Google Sheets con Coupler.io
Empieza gratis