A client of mine recently asked for my help in automating the data pulls they were doing. They wanted me to use the system's REST API to fetch new data and write it to a Google Sheet. The response payload of the API was pretty easy to navigate: essentially an array with a bunch of objects, where each object represented a user with certain keys for first and last names, phone, email -- that sort of thing. All I had to do was call the API periodically, get back new user objects, and them to a nested array so that I can append the data to Google Sheets.
Easy enough, but there was one wrinkle: The client informed me that the team would like to be able to change the structure of the Google Sheet from time ti time; mainly, to rename column headers, and move columns around (i.e, change the order of the columns in the sheet.
Clearly, my script would not be able to generate new rows based on the current headers, because those headers would change and subsequent data appends would put the wrong values in the wrong columns: suddenly an email columns would contain phone numbers – that sort of thing.
So how do we solve this? Well, one way is to create a map of API keys to column headers. We can create a new tab in the Google Sheet with two columns: one column represents the API, the other represents the sheet. We can ask the client to update the mapping every time they change their main spreadsheet. This way, the script can get the new API data, find the position of every key in the map, and construct the new rows based on the current order of the headers.
While some clients are open to such an idea – heck, many times clients have explicitly asked to maintain a mapper – I don't think it's cool to impose this extra responsibility on them if we don't have to. And we really don't: We can build and maintain the mapping ourselves. Let's see how.
Before creating the mapper, we need to be able to handle the column changes that users will make. Open the Apps Script IDE, and in the Code.gs file, enter the code below:
function onEdit(e) {
const thisSheet = e.source.getActiveSheet();
const thisName = thisSheet.getName();
if (thisName === 'Log') {
const thisRow = e.range.getRow();
const thisValue = e.range.getValue();
const oldValue = e.oldValue;
if (thisRow === 1 && oldValue && thisValue) {
// TODO: renameHeader(oldValue, thisValue);
} else {
// TODO: reorderHeaders();
}
}
}onEdit is the name of the function that Apps Script will call whenever users make changes in the Google Sheet, so it must be named exactly that. e represents the event object that holds information about the change.
We get the name of the sheet and check if it's named "Log" (or whatever the sheet is named.) If so, then we get the row number of the change, the new value of the cell and the old value of the cell, from before the change.
If the change happens in the first row and there are an old value and a new value then we know that we're handling a cell edit, and will therefore need to update the name of the header in our map. Otherwise, we may be looking at a column shuffling, in which case we need to update the position of each header in our map.
Ok, it's time to create our mapper. Here goes:
function createMap() {
const headers = {
id: { p: 0, n: 'ID' },
fn: { p: 1, n: 'First Name' },
ln: { p: 2, n: 'Last Name' },
ad: { p: 3, n: 'Address' },
em: { p: 4, n: 'Email' },
ph: { p: 5, n: 'Phone' },
jt: { p: 6, n: 'Occupation' },
co: { p: 7, n: 'Company' },
};
setHeaders(headers);
}We have a "headers" object with keys that correspond with the API keys. For each API key, I have a position "p" and a name "n" of the corresponding header in the spreadsheet. For instance, "fn" represents the "First Name" header that is currently in column B (position 1).
setHeaders is a function that I will create next to persist the map and make sure it's available every time I write new rows to the spreadsheet. I need to run createMap only once. From there on, the map will be updated according to the onEdit events.
Let's handle setting and getting the map:
function setHeaders(headers) {
const scriptProps = PropertiesService.getScriptProperties();
scriptProps.setProperty('headers', JSON.stringify(headers));
}
function getHeaders() {
const scriptProps = PropertiesService.getScriptProperties();
const headers = JSON.parse(scriptProps.getProperty('headers'));
console.log(headers);
return headers;
}setHeaders uses PropertiesService, which is a terrific Apps Script feature to persist data that the script needs, without writing the data to the sheet. Think of it as environment variables: you can store and read these variables during the normal run of the script (you can also manage the properties manually in the "Project Settings" tab of the GAS IDE.
We take the headers and stringify them. Then we create a property named headers, with the stringified map as the value. We also have a getHeaders function to pull the string from script properties, parse it, log it and return it. if you run createMap and then getHeaders, you should see the map logged in the console, just as we defined it.
Let's create the functions to update the map (our two to-dos inside "onEdit".)
function renameHeader(oldValue, thisValue) {
const headers = getHeaders();
for (const [k, v] of Object.entries(headers)) {
if (v.n === oldValue) {
headers[k].n = thisValue;
setHeaders(headers);
break;
}
}
}
function reorderHeaders() {
const headers = getHeaders();
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Log');
const headerRow = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues().flat();
headerRow.forEach((h, i) => {
for (const [k, v] of Object.entries(headers)) {
if (v.n === h) {
headers[k].p = i;
break;
}
}
});
setHeaders(headers);
}renameHeader accepts the old value and new value from "onEdit." It gets the map, and then iterates over the map's entries. when it finds a key with the name of the old value, it updates to the new value. It then persists the map and breaks.
reorderHeaders pulls the header row from the sheet, and, going header by header, it updates the position of the header in the map. It too persists the map.
We now have the code necessary for the map to keep up with the changes that users make in the structure of the sheet. Let's take advantage of the map to insert new rows correctly:
function appendDataToSheet() {
let newData = fetchNewDataFromAPI();
const headers = getHeaders();
newData = newData.map((obj) => {
const row = [];
for (const [k, v] of Object.entries(obj)) {
const p = headers[k].p;
row[p] = v;
}
return row;
});
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Log');
const newRow = sh.getLastRow() + 1;
sh.getRange(newRow, 1, newData.length, newData[0].length)
.setValues(newData);
SpreadsheetApp.flush();
}in the code above, we get the new data from the API. We then get our headers map. We change our array of objects to a nested array. We go over the objects one by one, create a new row for each, get the set of keys and values from the object, get the position of the API key from the map and insert the value into the right position in "row." Finally, we return the row. We then get the "Log" sheet and append the data to the end of the sheet.
I hope this gives an idea on how to keep up with changes that users make to spreadsheets that your script works with.