{"id":3953,"date":"2022-08-10T12:04:16","date_gmt":"2022-08-10T12:04:16","guid":{"rendered":"https:\/\/pickyassist.com\/blog\/?p=3953"},"modified":"2022-08-10T12:04:19","modified_gmt":"2022-08-10T12:04:19","slug":"google-sheet-value-update-script","status":"publish","type":"post","link":"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/","title":{"rendered":"Google Sheet Value Update Script"},"content":{"rendered":"\n

This article demonstrate how you can send WhatsApp message from google sheet and once the message is submitted to Picky Assist then update a value back to the google sheet specified column.<\/p>\n\n\n\n

This script is mainly used to solve following cases;<\/strong><\/p>\n\n\n\n

  1. If the google sheet is updated from any other 3rd party applications like Zapier or any update which are not performed by a user then trigger a WhatsApp message. (Google Scripts which we have published will work only if the value is updated by a human)<\/li>
  2. Sending bulk messages (30 messages per minute) from google sheet based on certain values becomes true, for example from a list of records you want to send messages only to few selected records<\/li><\/ol>\n\n\n\n

    Google Script <\/h2>\n\n\n\n

    Make Changes for the values which are shown under “CONFIG” <\/p>\n\n\n\n

    function Picky_Scheduled_Alert() {\n\n\/***-- CONFIG --***\/\n\n\tvar TargetColumn = 'J';\n\n\tvar SheetName = '';  \n\n\tvar Trigger_value = '';  \n\n\tvar Final_value = 'DONE'; \n\n        var TimeStamp_Column = '';\n\n\tvar Triger_name = 'Scheduled_Alert';\n\n\tvar Triger_url = 'https:\/\/webhook.site\/44757124-5c2a-4c41-a63f-7a138b914ce8';\n\n\/****************************\/\n\n\n\tif( TargetColumn == '' || Triger_url == '')\n\t\treturn false;\n\n\tvar tarCol = 0, length = TargetColumn.length;\n\n\tfor (var i = 0; i < length; i++)\n\t{\n\t\ttarCol += (TargetColumn.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);\n\t}\n\n  var TimeStamp_Col = -1;\n\n  var time_stamp = '';\n\n  if( TimeStamp_Column != '' ){\n\n    \tvar TimeStamp_Col = 0, length = TimeStamp_Column.length;\n\n      for (var i = 0; i < length; i++)\n      {\n        TimeStamp_Col += (TimeStamp_Column.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);\n      }\n\n\n      var timeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();\n\n      var tarDate = new Date();\n\n      time_stamp  = tarDate.toLocaleString(\"en-US\", {timeZone: timeZone})\n\n  }\n\n\n\tif( SheetName == '')\n\t\tvar sheet = SpreadsheetApp.getActive().getSheets()[0];\n\telse\n\t\tvar sheet = SpreadsheetApp.getActive().getSheetByName(SheetName);\n\t\n\n\tvar startRow = 1;\n\tvar lastRow = sheet.getLastRow();\n\tvar lastCloumn = sheet.getLastColumn()\n\n\n\tfor (var i =startRow ; i <= lastRow; i++) {\n\n\t\tvar c_val = sheet.getRange(i, tarCol).getValue();\n\t\t\/\/Logger.log(c_val);\n\n\t\ttry{\n\n\t\t\tif( c_val == Trigger_value ){\n\n\t\t\t\tLogger.log(\"TRIGGER VALUE FOUND:\", c_val);\n\n\t\t\t\t\n\n\t\t\t\tvar formData = {\n\t\t\t\t\t\t'triger_name': Triger_name\n\t\t\t\t};\n\n\t\t\t\tvar range1 = sheet.getRange(i, 1, 1, lastCloumn);\n\t\t\t\tvar values = range1.getDisplayValues();\n\n\t\t\t\tfor (var j = 0; j < sheet.getLastColumn(); j++){\n\n\t\t\t\t\tvar temp, letter = '';\n\n\t\t\t\t\tvar column = j+1;\n\n\t\t\t\t\twhile (column > 0) {\n\t\t\t\t\t\ttemp = (column - 1) % 26;\n\t\t\t\t\t\tletter = String.fromCharCode(temp + 65) + letter;\n\t\t\t\t\t\tcolumn = (column - temp - 1) \/ 26;\n\t\t\t\t\t}\n\n\t\t\t\t\tformData[letter] = values[0][j];\n\n\t\t\t\t}\n\n\t\t\t\tvar options = {\n\t\t\t\t\t\t'method' : 'post',\n\t\t\t\t\t\t'payload' : formData\n\t\t\t\t\t};\n\n\t\t\t\tUrlFetchApp.fetch(Triger_url, options);\n\t\t\t\tdelete formData;\n\n\n\t\t\t\tsheet.getRange(i, tarCol).setValue(Final_value);\n\n\n        if( TimeStamp_Col > -1 ){\n          sheet.getRange(i, TimeStamp_Col).setValue(time_stamp);\n        }\n\n\t\t\t}\n\n\t\t}catch(err) {\n\t\t\tcontinue;\n\t\t}\n\t\t\n\t} \n\t\n}<\/code><\/pre>\n\n\n\n
    Now we need to update the values from line number 5 to 17 as per different requiremen<\/strong>ts, below are the descriptions<\/strong><\/p>\n\n\n\n
    See the below areas i.e 5 to 15 lines where you need to make changes as per your requirements.<\/strong><\/p>\n\n\n\n
    \"\"<\/a><\/figure>\n\n\n\n
    Fields<\/strong><\/td>Description<\/strong><\/td><\/tr>
    var TargetColumn = \u2018D\u2019;<\/td>Here you need to select the column name the script should lookup for the value<\/td><\/tr>
    var SheetName = \u2018 \u2018;<\/td>If you have more than 1 sheet then please specify the sheet name here<\/td><\/tr>
    var Trigger_value = ‘ ‘; <\/td>This the value you need to setup in the sheet in the specified column, for example if you set as “Send” then the script will pick the columns containing “Send” text and send the details to the Picky Assist Connector<\/td><\/tr>
    var Final_value = ‘DONE’; <\/td>This is the value which needs to be updated back to the google sheet column so that the script don’t process the row again, you can add any values here and the same will be updated back when the script sends the informations to the connector for processing it<\/td><\/tr>
    var TimeStamp_Column = ‘ ‘;<\/td>This is an optional settings if you would like know at what time the record was send to the Picky Assist connector for processing then you can create a new column in your google sheet and specify the column name here.

    When the script read the data the time get updated to this field so that you can know that this was the last time that specific row details send to the Picky Assist Connector<\/td><\/tr>
    var Triger_name = \u2018alert\u2019;<\/td>You can give a name to the trigger which helps you to identify from which function the data is getting passed to the Picky Assist Connector so that you can set up the automation rules accordingly, useful when you have more than one function in a sheet.<\/td><\/tr>
    var Triger_url = \u2018https:\/\/pickyassist.com\/beta\/url\/e96ad4\u2019;<\/td>This is where you need to copy and paste the Connector URL<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    Setup Trigger in Google Form<\/strong><\/h2>\n\n\n\n
    The next step is to Configure the Trigger in the Google Sheet so that every time google will trigger the code as per the schedule you configured,\u00a0 to do this select the trigger menu from the left side menu bar and then click on\u00a0\u201cAdd Trigger\u201d\u00a0<\/strong>then configure it as seen in the below screenshot;<\/p>\n\n\n\n
    \"\"<\/a><\/figure>\n\n\n\n
    Variables<\/td>Description<\/td><\/tr>
    Choose which function to run<\/td>You have to select \u201cPicky_Scheduled_Alert\u201d if you have only one function, you have the option to duplicate the function by giving a different name to the function and once it\u2019s done you will able to see those functions in the drop down, this is useful if you have multiple functions in a script<\/td><\/tr>
    Which Runs at Deployment<\/td>Always make sure it\u2019s selected as \u201cHead\u201d<\/td><\/tr>
    Select Event Source<\/td>Always make sure it\u2019s \u201cTime Drive\u201d<\/td><\/tr>
    Select type of time based trigger<\/td>This is where you will get below options to select when the script needs to be run, if you would like to check for changes in every minutes then select the timer as “Minutes” recommended 10 minutes interval  

    Specific date and time
    Minutes Timer
    Hour Timer
    Day Timer
    Week Timer
    Month Timer<\/td><\/tr>
    Select minute interval<\/td>Based on the above selection we will see different options, since we selected \u201cMinute Timer\u201d now we need to select the minutes in which the trigger should execute

    Highly recommend to setup as 5 minutes interval so every 5 minutes the script will check for changes and if new changes are found then it execute the script and send details to the Picky Assist Connector.

    <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    After that click on \u201cSave\u201d button and that\u2019s it <\/strong><\/p>\n\n\n\n
    <\/a>Testing the Integration <\/h1>\n\n\n\n
    Now to setup the integration we need to map the Google Sheet data with Picky Assist Connector so that it can perform conditions and actions based on the data received from the Google Sheet.<\/p>\n\n\n\n
    Open the Google Sheet and change the value in the targeted column, then go to Script Editor from Extensions -> App Scripts and click on \u201cRun Icon\u201d<\/strong> to run the script as shown below;<\/p>\n\n\n\n
    \"\"<\/a><\/figure>\n\n\n\n
    Go to your Picky Assist Account then navigate to Connector, select the connector which was created for this Google Sheet then in the left sidebar you will able to see logs, this means Google notified the Picky Assist for the new update in the column.<\/p>\n\n\n\n
    <\/a>Mapping the JSON Data Structure<\/h2>\n\n\n\n
    In order to map the JSON data structure go to \u201cField Mapping\u201d then click on JSON Code from the popup you will able to see something shown as below;<\/p>\n\n\n\n
    \"\"<\/a><\/figure>\n\n\n\n
    If you are not seeing this option then please click on the \u201cCode\u201d <\/strong>tab then Click on \u201cMap\u201d<\/strong> button as shown below<\/p>\n\n\n\n
    \"\"<\/a><\/figure>\n\n\n\n
    <\/a>Creating Steps<\/h2>\n\n\n\n
    Now we need to create steps which take different action based on the value in the google sheet<\/strong>, in order to do this first let\u2019s create one step with all necessary filter and action then duplicate the steps for ease the process,<\/p>\n\n\n\n
    \"\"<\/a><\/figure>\n\n\n\n
    Once a step is configured, we can simply take duplicates of the steps and change the filter condition value and message content<\/p>\n","protected":false},"excerpt":{"rendered":"
    This article demonstrate how you can send WhatsApp message from google sheet and once the message is submitted to Picky Assist then update a value back to the google sheet specified column. This script is mainly used to solve following cases; If the google sheet is updated from any other 3rd party applications like Zapier […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[44,131,137],"tags":[],"class_list":["post-3953","post","type-post","status-publish","format-standard","hentry","category-guides","category-integrations","category-spreadsheets"],"yoast_head":"\nGoogle Sheet Value Update Script - Picky Assist Official Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Google Sheet Value Update Script - Picky Assist Official Blog\" \/>\n<meta property=\"og:description\" content=\"This article demonstrate how you can send WhatsApp message from google sheet and once the message is submitted to Picky Assist then update a value back to the google sheet specified column. This script is mainly used to solve following cases; If the google sheet is updated from any other 3rd party applications like Zapier […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/\" \/>\n<meta property=\"og:site_name\" content=\"Picky Assist Official Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-08-10T12:04:16+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-10T12:04:19+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/pickyassist.com\/blog\/wp-content\/uploads\/2022\/08\/image-7-1024x470.png\" \/>\n<meta name=\"author\" content=\"Danny\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Danny\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/\",\"url\":\"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/\",\"name\":\"Google Sheet Value Update Script - Picky Assist Official Blog\",\"isPartOf\":{\"@id\":\"https:\/\/pickyassist.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/pickyassist.com\/blog\/wp-content\/uploads\/2022\/08\/image-7-1024x470.png\",\"datePublished\":\"2022-08-10T12:04:16+00:00\",\"dateModified\":\"2022-08-10T12:04:19+00:00\",\"author\":{\"@id\":\"https:\/\/pickyassist.com\/blog\/#\/schema\/person\/f7e9e7ef306abb4d9a1b00210e2c86e1\"},\"breadcrumb\":{\"@id\":\"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/#primaryimage\",\"url\":\"https:\/\/pickyassist.com\/blog\/wp-content\/uploads\/2022\/08\/image-7.png\",\"contentUrl\":\"https:\/\/pickyassist.com\/blog\/wp-content\/uploads\/2022\/08\/image-7.png\",\"width\":2648,\"height\":1216},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/pickyassist.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Google Sheet Value Update Script\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/pickyassist.com\/blog\/#website\",\"url\":\"https:\/\/pickyassist.com\/blog\/\",\"name\":\"Picky Assist Official Blog\",\"description\":\"Picky Assist Official Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/pickyassist.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/pickyassist.com\/blog\/#\/schema\/person\/f7e9e7ef306abb4d9a1b00210e2c86e1\",\"name\":\"Danny\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/pickyassist.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/83c616c1e62e9b2f2177e7b029b7270bea92cfe313402e298ee4c0fa0e05a951?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/83c616c1e62e9b2f2177e7b029b7270bea92cfe313402e298ee4c0fa0e05a951?s=96&d=mm&r=g\",\"caption\":\"Danny\"},\"url\":\"https:\/\/pickyassist.com\/blog\/author\/adminreji_blog\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Google Sheet Value Update Script - Picky Assist Official Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/","og_locale":"en_US","og_type":"article","og_title":"Google Sheet Value Update Script - Picky Assist Official Blog","og_description":"This article demonstrate how you can send WhatsApp message from google sheet and once the message is submitted to Picky Assist then update a value back to the google sheet specified column. This script is mainly used to solve following cases; If the google sheet is updated from any other 3rd party applications like Zapier […]","og_url":"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/","og_site_name":"Picky Assist Official Blog","article_published_time":"2022-08-10T12:04:16+00:00","article_modified_time":"2022-08-10T12:04:19+00:00","og_image":[{"url":"https:\/\/pickyassist.com\/blog\/wp-content\/uploads\/2022\/08\/image-7-1024x470.png"}],"author":"Danny","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Danny","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/","url":"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/","name":"Google Sheet Value Update Script - Picky Assist Official Blog","isPartOf":{"@id":"https:\/\/pickyassist.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/#primaryimage"},"image":{"@id":"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/#primaryimage"},"thumbnailUrl":"https:\/\/pickyassist.com\/blog\/wp-content\/uploads\/2022\/08\/image-7-1024x470.png","datePublished":"2022-08-10T12:04:16+00:00","dateModified":"2022-08-10T12:04:19+00:00","author":{"@id":"https:\/\/pickyassist.com\/blog\/#\/schema\/person\/f7e9e7ef306abb4d9a1b00210e2c86e1"},"breadcrumb":{"@id":"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/#primaryimage","url":"https:\/\/pickyassist.com\/blog\/wp-content\/uploads\/2022\/08\/image-7.png","contentUrl":"https:\/\/pickyassist.com\/blog\/wp-content\/uploads\/2022\/08\/image-7.png","width":2648,"height":1216},{"@type":"BreadcrumbList","@id":"https:\/\/pickyassist.com\/blog\/google-sheet-value-update-script\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/pickyassist.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Google Sheet Value Update Script"}]},{"@type":"WebSite","@id":"https:\/\/pickyassist.com\/blog\/#website","url":"https:\/\/pickyassist.com\/blog\/","name":"Picky Assist Official Blog","description":"Picky Assist Official Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/pickyassist.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/pickyassist.com\/blog\/#\/schema\/person\/f7e9e7ef306abb4d9a1b00210e2c86e1","name":"Danny","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/pickyassist.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/83c616c1e62e9b2f2177e7b029b7270bea92cfe313402e298ee4c0fa0e05a951?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/83c616c1e62e9b2f2177e7b029b7270bea92cfe313402e298ee4c0fa0e05a951?s=96&d=mm&r=g","caption":"Danny"},"url":"https:\/\/pickyassist.com\/blog\/author\/adminreji_blog\/"}]}},"_links":{"self":[{"href":"https:\/\/pickyassist.com\/blog\/wp-json\/wp\/v2\/posts\/3953","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pickyassist.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pickyassist.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pickyassist.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pickyassist.com\/blog\/wp-json\/wp\/v2\/comments?post=3953"}],"version-history":[{"count":13,"href":"https:\/\/pickyassist.com\/blog\/wp-json\/wp\/v2\/posts\/3953\/revisions"}],"predecessor-version":[{"id":3970,"href":"https:\/\/pickyassist.com\/blog\/wp-json\/wp\/v2\/posts\/3953\/revisions\/3970"}],"wp:attachment":[{"href":"https:\/\/pickyassist.com\/blog\/wp-json\/wp\/v2\/media?parent=3953"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pickyassist.com\/blog\/wp-json\/wp\/v2\/categories?post=3953"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pickyassist.com\/blog\/wp-json\/wp\/v2\/tags?post=3953"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}