What is your goal?
I have a Google Sheet where I need to add a new column on the right side and update the data in the existing rows.
What is the problem & what have you tried?
The idea is that every month I need to update subscriber data from Facebook pages and publish it into my spreadsheet. In other words, new columns on the right should be added automatically once the current table reaches the end of the existing data. For example, if the current month is June, a new July column should be added, and so on. In the screenshot, I highlighted the table structure and the column that I want to append to the existing table.
At the same time, I have a fixed number of rows that remains unchanged, and historical data from previous months should not be overwritten.
I don’t understand how this can be implemented in one scenario.
Currently, I have a semi-automatic process: I export the data into another spreadsheet and manually fill in my main spreadsheet.
Screenshots (scenario flow, module settings, errors)
the reason this is fiddly is that make’s native google sheets modules are all row-based, add a row, update a row, so they can’t naturally push a new column. the append-to-the-right thing has to go through the sheets api directly.
what i’d do: run a scheduled scenario once a month, first step reads row 1 to find the last filled column so you know the next empty one. then use the google sheets “make an api call” module (values.update) with a computed range like SheetName!1: and drop in the month header plus the subscriber values in one write. since your row count is fixed that range is easy to build.
the facebook side is just a get-page-insights call for page_fans feeding those values in before the write.
only real gotcha is computing the column letter past Z (AA, AB) if it runs for years, but that’s a small formula. happy to help you wire the api-call module and the column logic if you want a hand, that’s the part that trips most people up.
Hello,
How data is added to your sheet? Is it done manually or using automation?
To be honest - everything depends on the process.
Simplest thing?
If we assume that there is no extra column so every time it is current month +1 you can find which column is last using a custom API call:
Method: GET
spreadsheets/YOUR_SPREADSHEET_ID/values/YOUR_SHEET_NAME!1:1
It will return array of values in your columns.
Then you can simply count them using length() function.
To add new values you shall make next API call:
Method: POST
spreadsheets/YOUR_SPREADSHEET_ID:batchUpdate
With body:
"requests": [
{
"updateCells": {
"start": {
"sheetId": 0,
"rowIndex": 1,
"columnIndex": {{length(6.body.values[])}}
},
"rows": [
{
"values": [
{
"userEnteredValue": {
"stringValue": "Test"
}
}
]
}
],
"fields": "userEnteredValue"
}
}
]
}
Where:
"sheetId": 0, is your sheet ID (Sheet1 is by default 0), you can check sheet ID in your browser URL bar
"rowIndex": 1, is your first row to update. Row 1 = 0 so if you have headers in A1:Z1 it will be 1.
"columnIndex": {{length(6.body.values[])}} is your column selector. In our case we are referring to array length and the same as with rows - column A is 0.
Benefits of this approach? You can use Array Aggregator and add multiple values with one request.
