{"id":12493,"date":"2020-05-27T09:31:56","date_gmt":"2020-05-27T06:31:56","guid":{"rendered":"https:\/\/railsware.com\/blog\/?p=12493"},"modified":"2022-11-25T14:25:01","modified_gmt":"2022-11-25T11:25:01","slug":"automatic-data-import-via-api","status":"publish","type":"post","link":"https:\/\/railsware.com\/blog\/automatic-data-import-via-api\/","title":{"rendered":"How to Import Users\u2019 Data into Email Sending Platforms via API"},"content":{"rendered":"\n<p>In this article, we describe how to automate importing users data from BigQuery to MailerLite (an email sending service) via API using <a href=\"https:\/\/www.coupler.io\/\" target=\"_blank\" rel=\"noopener noreferrer\">\u0421oupler.io<\/a>, GSheets and Apps Script. This approach will help you save time on collecting users\u2019 data from various sources in a single email audience.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><a href=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Google-Apps-Script-article-Illustration.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"2400\" height=\"1260\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Google-Apps-Script-article-Illustration.jpg\" alt=\"rw-apps-script-article-Illustration\" class=\"wp-image-12508\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Google-Apps-Script-article-Illustration.jpg 2400w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Google-Apps-Script-article-Illustration-360x189.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Google-Apps-Script-article-Illustration-1024x538.jpg 1024w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Google-Apps-Script-article-Illustration-768x403.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Google-Apps-Script-article-Illustration-1536x806.jpg 1536w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Google-Apps-Script-article-Illustration-2048x1075.jpg 2048w\" sizes=\"auto, (max-width: 2400px) 100vw, 2400px\" \/><\/a><\/figure><\/div>\n\n\n<h4 class=\"wp-block-heading\">Step-by-step plan for data import setup<\/h4>\n\n\n\n<ul class=\"wp-block-list\"><li>Export data from BigQuery to GSheets using Coupler.io<\/li><li>Set up data structure in GSheets<\/li><li>Set up data structure in MailerLite to match the GSheets structure<\/li><li>Export data from GSheets to MailerLite:\n<ul>\n<li>Get data from spreadsheet<\/li>\n<li>Connect and send data to external API (MailerLite)<\/li>\n<li>Log API response data to GSheets (to collect time logs)<\/li>\n<li>Automate your Google Apps Script using time triggers.<\/li>\n<li>Export data from BigQuery to GSheets using Coupler.io<\/li>\n<\/ul>\n<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Export data from BigQuery to GSheets using Coupler.io<\/h3>\n\n\n\n<p>Our product gathers users&#8217; data in BigQuery. We import data from BigQuery to GSheets using <a href=\"https:\/\/www.coupler.io\/\" target=\"_blank\" rel=\"noopener noreferrer\">\u0421oupler.io<\/a>. It takes only a few minutes to set it up. You can select how often you want to import data from BigQuery, and Coupler.io will do it automatically without reducing GSheets efficiency. To practice, download a <a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1BMwhc4myW1F2V_nnN2HqkUT9NmbU5k6kc-NkuM9g0hQ\/edit?usp=sharing\" target=\"_blank\" rel=\"noopener noreferrer\">data set with fake users\u2019 data<\/a>. If you want to move data in a reverse direction &#8211; <a href=\"https:\/\/www.coupler.io\/bigquery-integrations\" target=\"_blank\" rel=\"noopener noreferrer\">integrate with BigQuery<\/a> &#8211; Coupler.io can do this as well!<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><a href=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/rw-apps-coupler.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"2400\" height=\"1260\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/rw-apps-coupler.jpg\" alt=\"rw-apps-coupler\" class=\"wp-image-12496\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/rw-apps-coupler.jpg 2400w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/rw-apps-coupler-360x189.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/rw-apps-coupler-1024x538.jpg 1024w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/rw-apps-coupler-768x403.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/rw-apps-coupler-1536x806.jpg 1536w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/rw-apps-coupler-2048x1075.jpg 2048w\" sizes=\"auto, (max-width: 2400px) 100vw, 2400px\" \/><\/a><\/figure><\/div>\n\n\n<p>To export data directly from BigQuery to MailerLite, you may need help from a developer and a server. However, it is possible to build an automation yourself using GSheets as a mediator and Apps Script with triggers based on time. This way, the import will run automatically without a manual trigger.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Spreadsheet setup (initial source)<\/h3>\n\n\n\n<p>Our spreadsheet with data exported from BigQuery contains two tabs:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>\u201cUsers\u201d<\/strong> &#8211; here we import data from BigQuery using Coupler.io: name, profession, email, and a trigger to differentiate between email campaigns (1- for free users, 0 &#8211; for paid users).<\/li><\/ul>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><a href=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-2.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"2400\" height=\"1100\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-2.jpg\" alt=\"rw-apps-users\" class=\"wp-image-12499\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-2.jpg 2400w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-2-360x165.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-2-1024x469.jpg 1024w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-2-768x352.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-2-1536x704.jpg 1536w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-2-2048x939.jpg 2048w\" sizes=\"auto, (max-width: 2400px) 100vw, 2400px\" \/><\/a><\/figure><\/div>\n\n\n<ul class=\"wp-block-list\"><li><strong>\u201cLogs\u201d<\/strong> &#8211; here we will store the history of imports to double check when the data was sent and to monitor whether the automation worked. Prepare the needed headers in advance using your destination structure (in our case, MailerLite standard API responses).<\/li><\/ul>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><a href=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-4.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"2400\" height=\"700\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-4.jpg\" alt=\"rw-apps-logs\" class=\"wp-image-12500\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-4.jpg 2400w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-4-360x105.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-4-1024x299.jpg 1024w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-4-768x224.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-4-1536x448.jpg 1536w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-4-2048x597.jpg 2048w\" sizes=\"auto, (max-width: 2400px) 100vw, 2400px\" \/><\/a><\/figure><\/div>\n\n\n<p>We read the <a href=\"https:\/\/developers.mailerlite.com\/reference#add-many-subscribers\" target=\"_blank\" rel=\"noopener nofollow noreferrer\">MailerLite documentation<\/a> and we see that the API will respond with the <em>\u201cimported\u201d<\/em><em>, \u201cunchanged\u201d, \u201cupdated\u201d<\/em> and <em>\u201cerrors\u201d<\/em> logs. So we use the same structure in the logs headers.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>\u201cdatetime\u201d<\/strong> &#8211; custom, to know when each import has happened<\/li><li><strong>\u201cimported\u201d<\/strong> &#8211; how many new users have been added<\/li><li><strong>\u201cunchanged\u201d<\/strong> &#8211; how many of the same users appeared in this import<\/li><li><strong>\u201cupdated\u201d<\/strong> &#8211; how many users\u2019 data has changed (e.g. change of surname)<\/li><li><strong>\u201cerrors\u201d<\/strong><\/li><li><strong>\u201cerrors log\u201d<\/strong> &#8211; a detailed description of errors that happened during this import<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">MailerLite setup (destination source)<\/h3>\n\n\n\n<p>In MailerLite, we want to automatically shape audiences for email campaigns and use different variables to customize emails (e.g. mentioning the user\u2019s name in the email subject). The structure of the data in our initial source (i.e. spreadsheet) should match the structure in the destination source. So we first set up custom fields in MailerLite to have \u201cfull name\u201d, \u201cjob\u201d, and \u201ctrigger\u201d fields. We also need to create a group. Go to \u201cGroups\u201d -&gt; \u201cCreate group\u201d. Add a group name, and it\u2019s ready to go.<\/p>\n\n\n\n<p>To connect our spreadsheet to the MailerLite API, we need to get a token. Each app you use has its own token. So make sure to find it. In our case, we use Integrations -&gt; API use -&gt; get the token (\u201cAPI key\u201d) and \u201cSubscribers group ID\u201d to import our data to the correct group.<br>Save both parameters in your notes &#8211; we will need those in the Apps Script code.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Google Apps Script<\/h3>\n\n\n\n<p>Getting back to GSheets, open Tools -&gt; Script Editor. Each script is stored as a separate project in the \u201cMy Projects\u201d folder on the Google Apps Script Dashboard. Each of your Apps Script projects is linked to a specific spreadsheet.<\/p>\n\n\n\n<p>First, add the name of the project. Let\u2019s call it <em>\u201cExport data to MailerLite\u201d<\/em>. In the script editor window, we have the next categories:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>function<\/strong><\/li><li><strong>var<\/strong> &#8211; to declare variable (can be a number, text, true\/false), variable name is custom for you to read and use it further in the script<\/li><li><strong>\/\/ comment<\/strong><\/li><li><strong>return<\/strong> &#8211; what output we want to see<\/li><li><strong>const<\/strong> &#8211; to keep the values unchanged<\/li><\/ul>\n\n\n\n<p>We start from a standard naming <em>&#8220;myFunction()&#8221;<\/em>, which is customizable. You should change it to easily differentiate between functions you\u2019ve created in this spreadsheet. In the future, you can only use this function in this spreadsheet. Just type <em>\u201c=myFunction()\u201d<\/em> and it will work like any other formula in GSheets. Let\u2019s call this one <em>\u201cpostUsersData\u201d<\/em>.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><a href=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-1.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"2400\" height=\"1100\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-1.jpg\" alt=\"rw-apps-my-function\" class=\"wp-image-12512\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-1.jpg 2400w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-1-360x165.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-1-1024x469.jpg 1024w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-1-768x352.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-1-1536x704.jpg 1536w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-1-2048x939.jpg 2048w\" sizes=\"auto, (max-width: 2400px) 100vw, 2400px\" \/><\/a><\/figure><\/div>\n\n\n<p>You can transfer additional parameters in parentheses. For example, we have 2 accounts in MailerLite: for Coupler.io and for Mailtrap.io (both Railsware products). We want to transfer Coupler.io users\u2019 data to one MailerLite account, and Mailtrap.io users\u2019 data to a different one. In this case, we will add a token as a parameter for data transfer. So, next time we want to run an import, we will type <em>\u201c=postUsersData(_exact-token-to-import-data_)\u201d<\/em>. This provides us with additional flexibility and better structure for data imports. We won\u2019t be using this parameter in this case for the script to run automatically. If you want to activate the script manually, you can use various parameters.<\/p>\n\n\n\n<p>Our key functionality should be described in <em>{ }<\/em>. For the function to return a specific result based on the analysis, use the return keyword. The function will still run even without it. However, you won\u2019t get any presented result that can be used later.<br>The Apps Script works based on JavaScript. Unfortunately, Google does not provide the description of the JS syntax for Apps Script; they only describe the Google Sheets functions. But we have found some useful <a href=\"https:\/\/www.w3schools.com\/js\/js_functions.asp\" target=\"_blank\" rel=\"noopener nofollow noreferrer\">JavaScript materials<\/a> for you.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:javascript\">\/\/ Perform\n  const data = getDataFromSpreadsheet()\n  const response = postDataToMailerlite(data)\n  logAction(response)<\/pre>\n\n\n\n<p>According to our action plan, we need to build 3 functions:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><em>getDataFromSpreadsheet()<\/em><\/li><li><em>postDatatoMailerLite()<\/em><\/li><li><em>logAction()<\/em><\/li><\/ul>\n\n\n\n<p>We refer to the previous function outcomes by using them as a parameter. Except for these three, we will also build supportive functions <em>getSheetById()<\/em> and <em>makePayLoad()<\/em> &#8211; we will explain why a bit later. Place the supportive functions after the main functions. If you want to refer to a specific function in your script, the order does not matter much. We can place the functions in a more readable order, but we only need to name those after all the functions that have been built.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Get data from spreadsheet<\/h4>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><a href=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-8.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"2400\" height=\"380\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-8.jpg\" alt=\"rw-apps-link\" class=\"wp-image-12521\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-8.jpg 2400w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-8-360x57.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-8-1024x162.jpg 1024w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-8-768x122.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-8-1536x243.jpg 1536w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-8-2048x324.jpg 2048w\" sizes=\"auto, (max-width: 2400px) 100vw, 2400px\" \/><\/a><\/figure><\/div>\n\n\n<p>Build a function <em>getDataFromSpreadsheet()<\/em>. Add a GSheets \u201cUsers\u201d tab as a variable. Click on the tab and get the ID from the link (it differs for each tab). Add a comment that these sheets IDs can be changed if needed.<\/p>\n\n\n\n<p>Add another variable<em> usersSheet <\/em>pulled by ID. This should be done with the help of <em>getSheetById()<\/em> supportive function. In Google Apps Script, you cannot state the needed sheet by ID, only by its name. The problem is that anyone can change the name of your tab, so this is not a reliable parameter. Also, the code may not contain all the needed functions, so you have to create those yourself.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:javascript\">function getDataFromSpreadsheet() {\n    var usersSheetId = 11111111 \/\/ \/\/ Put your SHEET ID here\n    var usersSheet = getSheetById() \n<\/pre>\n\n\n\n<p>To address this issue, we add a supportive function getSheetById(). We found a ready-to-go <a href=\"https:\/\/stackoverflow.com\/questions\/26682269\/get-google-sheet-by-id\" target=\"_blank\" rel=\"noopener nofollow noreferrer\">solution for our task on StackOverFlow<\/a>. Do not hesitate to google ready-to-go solutions; there is no need to spend too much time on figuring those out yourself.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:javascript\">function getSheetById(id) {\n    \/\/ Get sheet by ID\n    return SpreadsheetApp.getActive().getSheets().filter(\n      function(s) {return s.getSheetId() === id;})[0];\n  }<\/pre>\n\n\n\n<p>We add a parameter that needs to be added manually &#8211; <em>getSheetById(id)<\/em>. The function takes the sheet ID and returns the correct spreadsheet based on this. Add a comment <em>\/\/ Get sheet by ID<\/em> to remind yourself what this function does.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><em>return<\/em> &#8211; for the function to return the needed spreadsheet by its ID.<\/li><li><em>SpreadsheetApp<\/em> &#8211; for function to return the spreadsheet results (not GDocs or Google Slides results, as Google Apps Script is connected to many GSuite products).<\/li><li><em>getActive()<\/em> is how the script connects to the active spreadsheet. Scripts can exist even without being connected to a specific spreadsheet.<\/li><li><em>getSheets()<\/em> pulls all of the sheets you have here.<\/li><li>Use <em>filter()<\/em> that contains a mini function that returns the sheet ID. You match this returned sheet ID with the <em>(id)<\/em> stated as a parameter in the very beginning on the higher-level function. We use <em>[0]<\/em> to return the first element of the function.<\/li><\/ul>\n\n\n\n<p>Now let\u2019s try using it in our main function. Add a new variable <em>usersSheet<\/em> equal to the newly created function <em>getSheetById<\/em> with <em>usersSheetId<\/em> parameter. Add a comment to remind yourself that this one contains users data from BigQuery.<\/p>\n\n\n\n<p>Add one more variable <em>values<\/em>. This function pulls the sheet object, and we need to get it structured with columns and rows. We need to pull the data in the next sequence: get the needed sheet (<em>usersSheet<\/em>), <em>getDataRange<\/em> (as a set of coordinates with data, to exclude empty rows and columns), and <em>getValues<\/em>.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:javascript\">function getDataFromSpreadsheet() {\n    var usersSheetId = 11111111 \/\/ \/\/ Put your SHEET ID here\n    var usersSheet = getSheetById(usersSheetId) \n    \/\/ Users data from BigQuery  \n    var values = usersSheet.getDataRange().getValues()\n<\/pre>\n\n\n\n<p>In our case, each row is a single record. We want to shape a JSON for each record and send it to API. For this we will create a <em>makePayLoad()<\/em> supportive function. As parameters, we state the headers of columns and values (users\u2019 data). <em>makePayLoad()<\/em> is needed to shape a single record of users\u2019 information into a single JSON block for MailerLite to read it. This is a <a href=\"https:\/\/developers.mailerlite.com\/reference#add-many-subscribers\" target=\"_blank\" rel=\"noopener nofollow noreferrer\">MailerLite specific format<\/a>. For other apps, use their API documentation. The function receives one record as an input. We need to identify where in this record (row) the specific value is (for example, email). For this, we pull the headers using the <em>indexOf<\/em> function. It returns the order number of the needed column using the headers. Based on this order number, we pull the needed values for every single record.<\/p>\n\n\n\n<p>Now we use the <em>map<\/em> function to extrapolate the <em>makePayLoad()<\/em> function to each record in the data set.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:javascript\">function postUsersData() {\n  function getDataFromSpreadsheet() {\n    var usersSheetId = 11111111 \/\/ \/\/ Put your SHEET ID here\n    var usersSheet = getSheetById(usersSheetId) \n    \/\/ Users data from BigQuery  \n    var values = usersSheet.getDataRange().getValues()\n    \n    return values.map(function(value) {\n      return makePayload(values[0], value)\n    })\n  }\n  function getSheetById(id) {\n    \/\/ Get sheet by ID\n    return SpreadsheetApp.getActive().getSheets().filter(\n      function(s) {return s.getSheetId() === id;})[0];\n  }\n  function makePayload(headers, value) {\n    \/\/ Make single user data JSON from BigQuery data based on row number\n    return {\n      'email': value[headers.indexOf('email')],\n      'fields': {\n        'full_name': value[headers.indexOf('name')], \n        'job': value[headers.indexOf('job')],\n        'trigger': value[headers.indexOf('trigger')]\n      }  \n    }  \n  }\n  \/\/ Perform\n  const data = getDataFromSpreadsheet()\n  const response = postDataToMailerlite(data)\n  logAction(response)\n}\n<\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Connect and send data to external API<\/h4>\n\n\n\n<p>Our next function is <em>postDataToMailerLite()<\/em>. We need to set up parameters for our API.<br>Our variables here are:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><em>url<\/em> &#8211; the link to the MailerLite API<\/li><li><em>token<\/em> &#8211; the one we\u2019ve saved in the previous step<\/li><li><em>groupID<\/em> &#8211; the one we\u2019ve saved in the previous step<\/li><li><em>groupUrl<\/em> &#8211; the final URL to import users\u2019 data<\/li><\/ul>\n\n\n\n<p>Read the <a href=\"https:\/\/developers.mailerlite.com\/reference#add-many-subscribers\" target=\"_blank\" rel=\"noopener nofollow noreferrer\">MailerLite documentation<\/a> as an example of how the API group URL should be built. The structure will be different for each new application, so make sure to read the API reference before using it. For MailerLite, the <em>groupUrl<\/em> should have this structure: <em>= url + groupId + \u2018\/subscribers\/import\/\u2019<\/em> (to add text, use single quotes). A semicolon is not necessary for this script to work; however, this is a part of code culture. Also, if the text shifts, the function will still work, as the tool will identify each part as a different piece of code.<\/p>\n\n\n\n<p>To send the data to MailerLite, we need to shape JSON blocks for API transfer. We go to the <a href=\"https:\/\/developers.mailerlite.com\/reference#add-many-subscribers\" target=\"_blank\" rel=\"noopener nofollow noreferrer\">MailerLite documentation<\/a> again to see the requirements for the API connection parameters and add those to our script. Use <em>JSON.stringify<\/em> function to state that this is a JSON format. Not all APIs can recognize the JavaScript objects as a JSON directly.<\/p>\n\n\n\n<p>Google Apps Script has a special function to get data from a URL. In our case, we need to use the <em>groupUrl<\/em> variable as the destination URL address. Add a response variable equal to <em>UrlFetchApp.fetch <\/em>function that connects to the stated URL using optional parameters.<\/p>\n\n\n\n<p>Once we\u2019ve sent the data, we should receive a response from the API server. We need to unpack this response with the help of the <em>getContentText()<\/em> function. The final step is to convert text data to JSON format via <em>JSON.parse()<\/em> to use the JSON object properties later.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:javascript\">function postDataToMailerlite(payload) {\n    \/\/ API data\n    var url = 'https:\/\/api.mailerlite.com\/api\/v2\/groups\/'\n    var token = 'xxxxxxxxxxxxxxx' \/\/ Put your TOKEN here\n    var groupId = 11111111 \/\/ \/\/ Put your GROUP ID here\n    var groupUrl = url + groupId + '\/subscribers\/import'\n    var params = {\n      'method': 'POST',\n      'muteHttpExceptions': true,\n      'headers': {\n        'Authorization': 'apikey ' + token,\n        'Content-Type': 'application\/json'\n      },\n      'payload': JSON.stringify({'subscribers': payload, 'resubscribe': false, 'autoresponders': true})\n    };\n    var response = UrlFetchApp.fetch(groupUrl, params)\n    return JSON.parse(response.getContentText())\n  }\n<\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Collect time logs via export of script data to GSheets<\/h4>\n\n\n\n<p>As you remember, we want to collect the import logs in a separate tab in GSheets. Let\u2019s create the <em>logAction()<\/em> function. We need to get the \u201cLogs\u201d sheet by its ID. Create a new variable <em>logsSheetId<\/em> and get the needed value from the tab link. Add a comment that it can be changed if needed.<\/p>\n\n\n\n<p>Add another variable <em>logsSheet<\/em> to easily refer to the needed sheet next time. Use our <em>getSheetById()<\/em> supportive function.The next variable <em>logsSheetsValues<\/em> reads the logs sheets\u2019 data by using the same sequence we used earlier:<em> logsSheet.getDataRange().getValues()<\/em><\/p>\n\n\n\n<p>To unpack the API server response, let\u2019s create a payload variable. We state the headers we have in the logs sheet in single quotes and state the needed data for each in this format:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><em>&#8216;datetime&#8217;: Date()<\/em> &#8211; this is a standard function in Apps Script<\/li><li>for other columns (<em>&#8216;imported&#8217;, &#8216;unchanged&#8217;, &#8216;updated&#8217;, &#8216;errors&#8217;<\/em>) we use <em>json.imported.length<\/em> to count the number of records per category<\/li><li>for <em>\u2018errors log\u2019<\/em> we use <em>json.errors<\/em> to get a description of those<\/li><\/ul>\n\n\n\n<p><em>Payload<\/em> acts as a dictionary consisting of values we\u2019ve received. The headers stated act as the keys. We use the set of functions <em>Object.keys(payload).foreach<\/em> to create a loop. The key target is to write each value from the dictionary into the right cell in the last row.<\/p>\n\n\n\n<p>Use <em>getRange()<\/em> to set the coordinates:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><em>logsSheetValues.length +1<\/em> &#8211; to add a new record to the last row<\/li><li><em>logsSheetValues[0].indexOf(key)+1<\/em> &#8211; to place values in the needed columns<\/li><li><em>setValue()<\/em> &#8211; to place the needed value into the needed cell<\/li><\/ul>\n\n\n\n<p>We added a <em>Logger.log()<\/em> function to see the status (\u2018Done\u2019) and the current date. Don\u2019t forget to save!<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:javascript\">function logAction(json) {\n    var logsSheetId = 11111111 \/\/ \/\/ Put your SHEET ID here\n    var logsSheet = getSheetById(logsSheetId)\n    var logsSheetValues = logsSheet.getDataRange().getValues()\n    var payload = {\n      'datetime': Date(),\n      'imported': json.imported.length,\n      'unchanged': json.unchanged.length,\n      'updated': json.updated.length,\n      'errors': json.errors.length,\n      'errors log': json.errors\n    }\n    Object.keys(payload).forEach(function(key) {\n       logsSheet.getRange(logsSheetValues.length + 1, logsSheetValues[0].indexOf(key) + 1).setValue(payload[key])\n    })\n    Logger.log('Done ' + Date())\n  }\n<\/pre>\n\n\n\n<p>Here is how the final script should look like:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:javascript\">function postUsersData() {\n  function getDataFromSpreadsheet() {\n    var usersSheetId = 11111111 \/\/ \/\/ Put your SHEET ID here\n    var usersSheet = getSheetById(usersSheetId) \n    \/\/ Users data from BigQuery  \n    var values = usersSheet.getDataRange().getValues()\n    \n    return values.map(function(value) {\n      return makePayload(values[0], value)\n    })\n  }\n  function postDataToMailerlite(payload) {\n    \/\/ API data\n    var url = 'https:\/\/api.mailerlite.com\/api\/v2\/groups\/'\n    var token = 'xxxxxxxxxxxxxxxxx' \/\/ \/\/ Put your TOKEN here\n    var groupId = 11111111 \/\/ \/\/ Put your GROUP ID here\n    var groupUrl = url + groupId + '\/subscribers\/import'\n    var params = {\n      'method': 'POST',\n      'muteHttpExceptions': true,\n      'headers': {\n        'Authorization': 'apikey ' + token,\n        'Content-Type': 'application\/json'\n      },\n      'payload': JSON.stringify({'subscribers': payload, 'resubscribe': false, 'autoresponders': true})\n    };\n    var response = UrlFetchApp.fetch(groupUrl, params)\n    return JSON.parse(response.getContentText())\n  }\n  function logAction(json) {\n    var logsSheetId = 11111111 \/\/ \/\/ Put your SHEET ID here\n    var logsSheet = getSheetById(logsSheetId)\n    var logsSheetValues = logsSheet.getDataRange().getValues()\n    var payload = {\n      'datetime': Date(),\n      'imported': json.imported.length,\n      'unchanged': json.unchanged.length,\n      'updated': json.updated.length,\n      'errors': json.errors.length,\n      'errors log': json.errors\n    }\n    Object.keys(payload).forEach(function(key) {\n       logsSheet.getRange(logsSheetValues.length + 1, logsSheetValues[0].indexOf(key) + 1).setValue(payload[key])\n    })\n    Logger.log('Done ' + Date())\n  }\n  function getSheetById(id) {\n    \/\/ Get sheet by ID\n    return SpreadsheetApp.getActive().getSheets().filter(\n      function(s) {return s.getSheetId() === id;})[0];\n  }\n  function makePayload(headers, value) {\n    \/\/ Make single user data JSON from BigQuery data based on row number\n    return {\n      'email': value[headers.indexOf('email')],\n      'fields': {\n        'full_name': value[headers.indexOf('name')], \n        'job': value[headers.indexOf('job')],\n        'trigger': value[headers.indexOf('trigger')]\n      }  \n    }  \n  }\n  \/\/ Perform\n  const data = getDataFromSpreadsheet()\n  const response = postDataToMailerlite(data)\n  logAction(response)\n}\n<\/pre>\n\n\n\n<p>Run the script. When you run it for the first time in a spreadsheet, the system will ask you to save your project. Click \u201cYes\u201d and give a name to your project. You\u2019ll see a yellow status line. Once it disappears this means that your function was completed. Press Ctrl + Enter (or Command + Enter for Mac) to see functions logs. In your GSheets \u201cLogs\u201d tab you\u2019ll see a new record with the current time and date. Also, in MailerLite you will see the new contacts added.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Automate your Google Apps Script using time triggers<\/h3>\n\n\n\n<p>The last step is to schedule the script to run automatically based on the time triggers. Click on the clock icon and choose &#8220;Current project triggers&#8221;. Add a trigger, choose the function you want to run from the list of functions connected to this spreadsheet. Select event source &#8211; this is what will act as a trigger. We chose \u201ctime-driven\u201d. Set a daytimer and select the exact time for the trigger to act. You can also set up a notifications schedule. Save and we are done!<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><a href=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-7.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"2400\" height=\"2000\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-7.jpg\" alt=\"rw-apps-time-trigger\" class=\"wp-image-12548\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-7.jpg 2400w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-7-360x300.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-7-1024x853.jpg 1024w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-7-768x640.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-7-1536x1280.jpg 1536w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Tabl-7-2048x1707.jpg 2048w\" sizes=\"auto, (max-width: 2400px) 100vw, 2400px\" \/><\/a><\/figure><\/div>","protected":false},"excerpt":{"rendered":"<p>Learn how to automate importing users&#8217; data from BigQuery to MailerLite via API using Coupler.io, Google Sheets, and Apps Script. This approach will help you save time on collecting users\u2019 data from various sources in a single email audience.<\/p>\n","protected":false},"author":80,"featured_media":12553,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[633,364],"tags":[],"coauthors":["Elvira Nassirova"],"class_list":["post-12493","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-analytics","category-labs"],"acf":[],"aioseo_notices":[],"categories_data":[{"name":"Data Analytics","link":"https:\/\/railsware.com\/blog?category=data-analytics"},{"name":"Labs","link":"https:\/\/railsware.com\/blog?category=labs"}],"post_thumbnails":"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2020\/05\/Google-Apps-Script-article-Illustration.jpg","amp_enabled":true,"_links":{"self":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts\/12493","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/users\/80"}],"replies":[{"embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/comments?post=12493"}],"version-history":[{"count":68,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts\/12493\/revisions"}],"predecessor-version":[{"id":15473,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts\/12493\/revisions\/15473"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/media\/12553"}],"wp:attachment":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/media?parent=12493"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/categories?post=12493"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/tags?post=12493"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/coauthors?post=12493"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}