loading...

Help with Google Sheets Script for Parsing JavaScript Objects

aquasar profile image Alex Quasar ・2 min read

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"]}]

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;
}

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.

Posted on by:

aquasar profile

Alex Quasar

@aquasar

React | Gatsby | Shopify | Online Advertising | Ecommerce | SEO

Discussion

markdown guide