AppsScriptPulse

Community News, Solutions, Sheets

💩 ShiitCoin: Putting a blockchain on Google Sheets with Google Apps Script

Tags:
| | 0 Comments |

Some ideas are bad, and then sometimes there are ideas so bad they actually go back around to being genius — we think we’ve come across one of those. Yes! We have an end to end blockchain working entirely off of a google sheet: transaction broadcasting, mining, wallets, gossip — all of it! ShiitCoin is (obviously) a troll project not meant to be rEaL mOnEy 💴 but we’re hoping to farm some internet points there’s some educational value to exploring the barebones of a blockchain which we’ll share here.

In pre-pandemic times I used to give a talk entitled ‘..you can do that with Apps Script’, which includes some more of the extreme examples of projects people tackle with Google Apps Script. This project by nalinbhardwaj (Nalin Bhardwaj) and Adhyyan1252 (Adhyyan Sekhsaria) definitely falls into the fun/interesting category and who knows you may find some interesting code snippets in the associated Github repo that might be useful … but no guarantees.   

Via Andrew Roberts

Source: ShiitCoin: Putting a blockchain on Google Sheets

Library, Solutions, Sheets, V8, Intermediate

SheetQuery: An ORM-Like Query Builder for Google Sheets and Google Apps Script

Tags:
| | 0 Comments |

Image credit: Vance Lucas

A simple and small Google Apps Script library for quickly and easily finding and updating records in Google Sheets with a familiar ORM-like syntax

Following our previous post on Converting Google Sheets cell values to an object array, Vance Lucas (@vlucas) got in touch to highlight the SheetQuery library he has created which as well as being able to get Google Sheet data as an object array has some additional nice features for updating cell values. As Vance highlights:

sometimes working with spreadsheets to find and update specific rows of data can be awkward and tedious. There is no direct built-in way to search for specific values in rows by headings. To do this with the Google-supplied APIs, you have to keep track of row index numbers, column index numbers, and arrays of row data while planning your updates. It’s not fun code to write, and it’s relatively error-prone, especially if you are deleting rows, which causes the row index numbers to shift dynamically.

The library includes a .where method which can be used to apply a filtering function to select the rows of a spreadsheet to be read and/or updated. If you are regularly developing scripts that interact with Google Sheets data this can be a great library to keep in mind.

Source: SheetQuery: An ORM-Like Query Builder for Google Sheets and Google Apps Script

Solutions, Sheets, V8, Intermediate

Converting Google Sheets cell values to an object array with heading keys using Google Apps Script

Tags:
| | 2 Comments |

This is a sample script for converting the values of Google Spreadsheet to an object using Google Apps Script.

I have a minor obsession with methods for converting Google Sheets data to an object array. Over the years I’ve documented various approaches I’ve developed on my personal site, like this one, but this solution from Kanshi Tanaike has to be my new favourite:

function myFunction() {
  const sheetName = "Sheet1";
  const [headers, ...rows] = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getDataRange()
    .getValues();
  const res = rows.map((r) =>
    headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {})
  );
  console.log(res);
}

Source: Converting Values of Google Spreadsheet to Object using Google Apps Script

Solutions, Admin SDK, Sheets, V8, Intermediate

List all users of a Google Workspace domain, remove inactive users and force reset users’ passwords – Digital Inspiration

Tags:
| | 0 Comments |

Amit Agarwal has recently been busy continuing to publish lots of incredibly useful Google Apps Script tips and snippets on his ‘Digital Inspiration’ blog. Three recent posts might be of particular interest to Google Workspace administrators:

[This Google Apps] Scripts gets the name and email address of users in the organization and saves the list inside a Google Spreadsheet. This script can only be executed by the domain administrator.

Source: List All Users of a Google Workspace Domain in Google Sheets – Digital Inspiration

Learn how the Google Workspace admin can change the Google account passwords of multiple users in their organization automatically with Google Apps Script.

Source: How to Force Reset Google Workspace Users’ Passwords with Apps Script – Digital Inspiration

Learn how to find inactive users in your Google Workspace domain and delete the dormant accounts to save on your monthly bills.

Source: Find and Remove Inactive Users in your Google Workspace Domain – Digital Inspiration

Solutions, Sheets, V8, Beginner

How to build an automated PDF report in Google Sheets using Apps Script 

Tags:
| | 0 Comments |

Grab this automated ConvertKit Report in Google Sheets to monitor your email list growth and use it to project your future list growth.

Ben Collins shares the solution he uses to produce a daily ‘ConvertKit’ PDF report. Even if you don’t use ConvertKit this post is still worth a read as the basic model of getting data from a third party API into Google Sheets and sending a PDF summary could come in handy. Perhaps not surprising given this post comes from Ben it includes some clever Google Sheet functions to prepare and shape data.

Source: Automated ConvertKit Report In Google Sheets

Solutions, Sheets, Video Tutorial, Beginner

Google Apps Script: How to export Google Sheets as PDF – YouTube 

Tags:
| | 0 Comments |

In this video you will see how you can use Google Apps Script to export a Google Sheets document as a PDF

It’s been a while since we featured a video tutorial from Chanel Greco, but that doesn’t mean she hasn’t been busy. In this recent tutorial from Chanel you can learn how to use Google Apps Script to export a Google Sheet as a PDF Document. You can watch this and other videos from Chanel on the saperis YouTube channel.

Source: Google Apps Script: How to export Google Sheets as PDF

Solutions, Forms, Sheets, V8, Beginner

Apps Script Basics – Using Form Responses – Learning Google Workspace & Apps Script

Tags:
| | 0 Comments |

In this post, we’re going to look at how we can work with the responses a form user submits.

We’ll look at two main ways, 1) Getting the form responses from a Google Sheet, 2) Getting the form responses directly from a Google Form.

To show some practical uses of this, we’ll do the following:

  1. Set up a simple problem-reporting log, which will email the relevant parties the problem in a classroom. We’ll do this via the Sheet and via the Form.
  2. Get students’ pieces of writing submitted via a Google Form and copy them to their individual sheets, ready to have feedback added.
  3. Set up a simple appointment system, which will update itself as people take the appointments, leaving only the available ones on the Form.

This post taken from Barrie Roberts’ latest book “Beginner’s Guide to Google Apps Script 2 – Forms“, available on Amazon here. The post covers some different ways that you can interact with Google Form responses and contains lots of useful code and tips that might be useful to other Google Apps Script developers.

Source: Apps Script Basics – Using Form Responses – Learning Google Workspace & Apps Script

Solutions, Sheets, V8, Beginner

[Google Sheets] Word Highlighter (made using Google Apps Script)

Tags:
| | 0 Comments |

I have created a handy highlighting tool using Google Sheets and Google Apps Script. The specification of this tool is very simple.

  1. The user pastes a message into the certain cell (A2).
  2. The tool checks if the message contains some words which listed in another dedicated sheet(Spam Word List).
  3. The tool highlights (bold and color red) the found words in the message.

Here is a nice post demonstrating how you can use Google Apps Script to highlight keywords within a Google Sheets cell. The solution uses  SpreadsheetApp.newTextStyle() to create a custom style which is then applied to part of the cell text.

Source: [Google Sheets] Word Highlighter

Solutions, Sheets, Beginner

How to retrieve company info in Google Sheets with Clearbit and Apollo API

Tags:
| | 0 Comments |

Image credit: Alexis Laporte

Scripting in Google Sheets gives you super power, and it’s very easy to use APIs to enrich your document.

In this article I’ll show you how-to:

  1. Create custom functions in Google Sheets
  2. Use Clearbit API to translate a company name into a company domain
  3. Use Apollo API to get details about a company using a company domain
  4. Bonus: Use Google CacheService to avoid API rate limits

We recently highlighted a Medium post on How to get Linkedin profiles in Google Sheets. Here is a similar example which combines data from the Clearbit and Apollo to return company information including other social media urls and more.

Image credit: Alexis Laporte

Some great tips in this post including using the Cache Service, find out more via the source link 👇.

Source: How to retrieve Company info in Google Sheets with Clearbit and Apollo API

Solutions, Sheets, V8, Beginner

How to get Linkedin profiles [and other Google Custom Search results] in Google Sheets with a formula

Tags:
| | 1 Comment |

Image credit: Giacomo Melzi

Some time ago, I made a custom Google Sheets formula to automatically get the Linkedin profile of a person/company. It can be helpful to anyone using Google Sheets as a CRM or recruitment, product feedback, or marketing tool. It saved me a lot of boring copy/pasting, so hopefully, it can save some time for you as well

Interesting project from Giacomo Melzi that uses the Google Custom Search API to search LinkedIn for personal and company profile pages. As noted by Giacomo:

Since Linkedin profiles are behind a login (you have to be logged in to check someone’s profile), the formula relies on a workaround. The script performs a domain-constrained search via the Google Custom Search API.

That’s something anyone can do manually in a standard Google search. Just try adding site:{domain} before your search query, and your results will be filtered by domain.

This technique can be used on other sites that are behind a login but indexed by Google. A couple of important limitations to be aware of, in particular, free quota limits. Check out the source post for more details and the code to run this yourself.

Source: How to get Linkedin profiles in Google Sheets with a formula