DEV Community 👩‍💻👨‍💻

DEV Community 👩‍💻👨‍💻 is a community of 963,274 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
Alex Quasar
Alex Quasar

Posted on

Help with Google Sheets Script for Parsing JavaScript Objects

I have thousands of rows of data in a Google Sheets File in a column that looks something like

[{"amountMax":49.99,"amountMin":49.99,"availability":"true","color":"Brown","currency":"USD","dateSeen":["2019-04-11T08:00:00Z"],"isSale":"false","offer":"Online only","sourceURLs":["https://www.walmart.com/ip/SadoTech-Model-CXR-Wireless-Doorbell-1-Remote-Button-2-Plugin-Receivers-Operating-500-feet-Range-50-Chimes-Batteries-Required-Receivers-Beige-Fixed-C/463989633"]}]
Enter fullscreen mode Exit fullscreen mode

I would like to be able to return the max value, the currency, the color attributes. How can I do that in Google Sheets. Ideally would like to do something like being able to retrieve the data attributes how I would normally in javascript like in this link here https://repl.it/@alexhoy/WetSlateblueDribbleware

However this does not seem to work for me when creating a function in script.google.com

For example, here is a slugify function which takes an input (cell) and turns it into a slug/handle without the need for looping. In Google Sheets I can then call =slugify(b2) and turn that value into slug form

/**
* Converts value to slug
* @customfunction
*/
function slugify(value) {
  /*
   * Convert the the vs in a range of cells into slugs.
   * @customfunction
   */
  let slug = '';

  slug = value.substring(0, 100).toLowerCase();
  slug = slug.replace(/[^\w\s-]/g, '');
  slug = slug.replace(/\s+/g, '-');
  Logger.log(slug);

  return slug;
}

Enter fullscreen mode Exit fullscreen mode

I want to do the same thing without looping to parse the object data above or declaring a range of values and what not.

Any suggestions on how I can do this in a simple way like shown above without the need for declaring active spreadsheet, range values and looping.

Top comments (0)

In defense of the modern web

I expect I'll annoy everyone with this post: the anti-JavaScript crusaders, justly aghast at how much of the stuff we slather onto modern websites; the people arguing the web is a broken platform for interactive applications anyway and we should start over;

React users; the old guard with their artisanal JS and hand authored HTML; and Tom MacWright, someone I've admired from afar since I first became aware of his work on Mapbox many years ago. But I guess that's the price of having opinions.