TIL JS in Google Sheets
POSTED ON:
TAGS: excel serverless googlesheets javascript scripting
As a developer, my job is to turn a 1 minute task into a two week project. For example: Why use Google Sheets when you can write 4,000 lines of JS with 3 JS libraries to re-create some data?
Anyways, I like this because I do use a lot of Google Sheets, and I write a lot of Javascript.
But to use JS in Google Sheets is... interesting! To use JS in Google Sheets to do serverless-like functions are... SUPER NEAT!
Joe Previte uses JS to do a cron-job to run this code every day to automate his Google Sheets work. And I think that's awesome.
Did you know you can run scripts on a schedule (similar to cron) from within your spreadsheets?
It's a way for you to automate tasks using macros.
Google will take you to their Editor inside App Scripts. This lets you use JS to interact with your spreadsheets.
I use a spreadsheet to keep track of updates for one of the @dipdotchat groups.
When a member sends an update in the Telegram group, it writes an x for that date in their column.
If they miss a day, the cell stays empty.
Manually, I've been opening the spreadsheet and changing empty cells to "-" which means no update.
A formula in the sheet uses the "-" cells to calculate the total charges.
I wrote a macro which runs daily at 9am and it:
- gets the row for the previous day
- updates the empty cells -> "-"
// macro.gs
/** @OnlyCurrentDoc */
const startDate = new Date("2021-06-16")
// so the first one is D2:K2
function MarkEmptyCellsInYesterdayRowWithHyphen() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Group 2 - B");
// Activates the sheet
SpreadsheetApp.setActiveSheet(sheet);
const today = new Date()
const diffInDays = days_between(today, startDate)
// Today, Jul 2 is 17, so tomorrow it will be 18.
// We only want to do it for the day before
// But since the first row in the sheet is the column
// it means July 2 is actually row 18
// Example: Today is July 2nd, 2021.
// There are 17 days between today and the start day
// If we were to run as is and use that number (17) for the row
// it would update 07/01/2021 because that's row 17
// Long story short, this works
const rowForDayBefore = `D${diffInDays}:K${diffInDays}`
const range = sheet.getRange(rowForDayBefore);
sheet.setActiveRange(range);
// Grab current row
const values = range.getValues()
// Update the empty cells with "-"
const updatedValues = updateValues(values)
range.setValues(updatedValues)
};
function updateValues(values) {
// If the value is empty, we mark it with "-"
// It looks like [ [ 'x', 'x', 'x', '', '', 'x', 'x', 'x' ] ]
// which is why we return in array, 2d array
return [values[0].map(v => v === "" ? "-" : v)]
}
// source: https://stackoverflow.com/a/2627482/3015595
function days_between(date1, date2) {
// The number of milliseconds in one day
const ONE_DAY = 1000 * 60 * 60 * 24;
// Calculate the difference in milliseconds
const differenceMs = Math.abs(date1 - date2);
// Convert back to days and return
return Math.round(differenceMs / ONE_DAY);
}
Macros & Google Spreadsheets 🪄
— Joe Previte (@jsjoeio) July 3, 2021
Did you know you can run scripts on a schedule (similar to cron) from within your spreadsheets?
It's a way for you to automate tasks using macros.
Here's a brief intro and how I'm testing them out with a real project 👇
Via: https://web.archive.org/web/20220107131254/https://twitter.com/jsjoeio/status/1411369162971045895
Related TILs
Tagged: excel