Sync Your Google Sheets Data to Botpress in One Cl...
# 📖tutorials
f
[YouTube Video](

https://youtu.be/h73DV0YYKNM

) Update your Botpress table by just clicking a button in your Google Sheet. No need to set up any automations, and its completely free of costs! By watching the video you will learn how to effortlessly synchronize your Google Sheets data with a Botpress table using a simple button click. This tutorial covers everything from setting up your Google Script to executing table updates without needing complex integrations. Perfect for those looking to automate their workflows and enhance data management in Botpress. No prior coding experience required! Code used:
Copy code
javascript
function syncTable() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let dataRange = sheet.getDataRange();
  let values = dataRange.getValues();
  
  for (let i = 1; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      if (values[i][j] !== "") {
        Logger.log("Row: " + (i+1) + ", Column: " + (j+1) + ", Value: " + values[i][j]);
      }
    }
  }
  
  deleteAllRowsInTable();
  createRowsInTable(values.slice(1));
}

function deleteAllRowsInTable() {
  let url = "https://api.botpress.cloud/v1/tables/table_TABLE ID HERE/rows/delete";
  
  let payload = {
    "deleteAllRows": true
  };
  
  let options = {
    "method": "POST",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer bp_pat_YOUR PAT HERE",
      "x-bot-id": "YOUR BOT ID HERE"
    },
    "payload": JSON.stringify(payload)
  };
  
  let response = UrlFetchApp.fetch(url, options);
  let result = JSON.parse(response.getContentText());
  
  Logger.log("Deleted rows: " + result.deletedRows);
}

function createRowsInTable(values) {
  let url = "https://api.botpress.cloud/v1/tables/table_YOUR TABLE ID HERE/rows";
  
  let rows = [];
  for (let i = 0; i < values.length; i++) {
    let row = {};
    row["userName"] = values[i][0];
    row["password"] = values[i][1];
    row["location"] = values[i][2];
    rows.push(row);
  }
  
  let payload = {
    "rows": rows
  };
  
  let options = {
    "method": "POST",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer bp_pat_YOUR PAT HERE",
      "x-bot-id": "YOUR BOT ID HERE"
    },
    "payload": JSON.stringify(payload)
  };
  
  let response = UrlFetchApp.fetch(url, options);
  let result = JSON.parse(response.getContentText());
  
  Logger.log("Created rows: " + result.rows.length);
  
  if (result.warnings.length > 0) {
    Logger.log("Warnings: " + result.warnings.join(", "));
  }
  
  if (result.errors.length > 0) {
    Logger.log("Errors: " + result.errors.join(", "));
  }
}
Ideas for further improvement: Update table schema to fit the Google Sheets data. So dynamically changing the amount of columns and column types
q
Genius idea!
a
Fabulous. Thank You! 🙌
f
Thank you, and you are very welcome! 🙂
m
Hi, I apologize in advance for this question, I'm new to Botpress, and I wanted to ask why you use this feature, what purpose does it serve, and what problem does it solve?
f
Hey there Mack, welcome and no need to apologize at all 🙂 There can be a lot of different reasons why someone would use a Google sheet, and then update their table based on this Sheet. I believe that the most common reason is that it's a lot easier to give a client access to a Google Sheet than a table in Botpress. This sheet could include products or passwords or something third that a client would have to update and that would be used in the chatbot. So with this tutorial the client can update the cells, click on the button and the table will also be updated.
c
awesome tutorial ⚡
f
Thank you!
a
I have been asked to supply a bot to book, document and record training course outcomes. This routine enables assessors to amend course dates, add details, add outcomes themselves. On top of that sheet changes trigger emails too. 😀
a
Works like magic @fresh-fireman-491 , thank you so much for you time and your work!!!
f
That is amazing to hear! and you are very welcome
f
@fresh-fireman-491 this is so sick. Like seriously. This actully makes tables useful now. Now im going to have to learn more about tables and start implementing them in to bots! 🤣
c
Lets go 🚀
f
That is amazing to hear!
m
Thank you for you respond 🙂 Great features💪
Thanks for your explain and example 🙂
3 Views