Owner of Hammies, former Jupyter core developer and co-lead of Jupyter Notebook and JupyterLab teams
spreadsheet = async (spreadsheetId) => {
return {
getRange: (range) => getRange(range, spreadsheetId),
updateRange: (range, values) => updateRange(range, values, spreadsheetId)
};
}
spreadsheetView = spreadsheetId => {
const value = spreadsheet(spreadsheetId);
const element = md`https://docs.google.com/spreadsheets/d/${spreadsheetId}`;
element.value = value;
return element;
}
getRange = async (range, spreadsheetId) => {
try {
if (Array.isArray(range)) {
const { result } = await client.sheets.spreadsheets.values.batchGet({
spreadsheetId,
ranges: range,
valueRenderOption
});
return result.valueRanges.reduce(
(result, { range, values }) => ({
...result,
[range]: rangeToObject(values)
}),
{}
);
}
const { result } = await client.sheets.spreadsheets.values.get({
spreadsheetId,
range,
valueRenderOption
});
return rangeToObject(result.values);
} catch (error) {
console.error(error.result.error);
return new Error(error.result.error.message);
}
}
updateRange = async (range, _values, spreadsheetId) => {
try {
const values = objectToRange(_values);
const { result } = await client.sheets.spreadsheets.values.update({
spreadsheetId,
range,
valueInputOption,
responseValueRenderOption,
resource: { values }
});
} catch (error) {
console.error(error.result.error);
return new Error(error.result.error.message);
}
}
valueRenderOption = "FORMATTED_VALUE"
responseValueRenderOption = "FORMATTED_VALUE"
valueInputOption = "USER_ENTERED"
gapi = new Promise((resolve, reject) => {})
client = {
await gapi.client.load(
"https://sheets.googleapis.com/$discovery/rest?version=v4"
);
return gapi.client;
}
rangeToObject = (range) => {
const columns = range[0];
return range.slice(1).map((row) =>
columns.reduce((result, column, index) => {
let value = row[index] || null;
if (value === "TRUE") {
value = true;
}
if (value === "FALSE") {
value = false;
}
return { ...result, [column]: value };
}, {})
);
}
rangeToMap = (range) => {
const columns = range[0];
return range.slice(1).map((row) =>
columns.reduce((result, column, index) => {
let value = row[index] || null;
if (value === "TRUE") {
value = true;
}
if (value === "FALSE") {
value = false;
}
result.set(column, value);
return result;
}, new Map())
);
}
objectToRange = (data) => {
const columns = Object.keys(data[0]);
return [
columns,
...data.map((row) => {
const values = columns.map((column) => {
const value = row[column];
if (!value) {
return "";
}
if (Array.isArray(value)) {
return value.join(",");
}
if (typeof value == "boolean" && value === true) {
return "TRUE";
}
if (typeof value == "boolean" && value === false) {
return "FALSE";
}
if (value instanceof Date) {
return value.toISOString().replace(/T.+/, "");
}
if (typeof value === "object") {
return JSON.stringify(value);
}
return value;
});
return values;
})
];
}
mapToRange = (data) => {
const columns = [...data[0].keys()];
return [
columns,
...data.map((row) => {
const values = columns.map((column) => {
const value = row.get(column);
if (!value) {
return "";
}
if (Array.isArray(value)) {
return value.join(",");
}
if (typeof value == "boolean" && value === true) {
return "TRUE";
}
if (typeof value == "boolean" && value === false) {
return "FALSE";
}
if (typeof value === "object") {
return JSON.stringify(value);
}
return value;
});
return values;
})
];
}
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
