[YouTube Video]() 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:

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(", "));
  }
}

Genius idea!

Fabulous. Thank You! 🙌

Thank you, and you are very welcome! 🙂

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?

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.

awesome tutorial ⚡

Thank you!

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. 😀

Works like magic @fresh-fireman-491 , thank you so much for you time and your work!!!

That is amazing to hear! and you are very welcome

@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! 🤣

Lets go 🚀

That is amazing to hear!

Thank you for you respond 🙂 Great features💪

this is amazing @fresh-fireman-491 thanks for sharing... just one question... how will you trigger this automatically to have your botpress table updated with the google sheet everyday, without the need to manually click on the button? can Make help you to schedule it?

Decay's solutions are always the best! 🦸🏼‍♂️ These might also give you some ideas. Here’s how we did it from Botpress to Google Sheets using Make.com https://discord.com/channels/1108396290624213082/1194161232052244521

message has been deleted

I completely agree with our amazing devmik here!🦸‍♂️ 💎

I just tested it with the original version of @fresh-fireman-491 and scheduled it with an Apps script trigger and it works perfectly... skipping Make.com as explained by @quick-musician-29561 .. you guys are the best 🏆 thank you!!!

Amazing, and well done!🦾 🛠️

Hi @fresh-fireman-491 I followed your tutorial and it works, but it's giving me an error whenever I have more than 1000 rows. Is there any way around that?

Glad to hear that it works. Which error is it giving you?

It's giving me this: Exception: Request failed for https://api.botpress.cloud returned code 400. Truncated server response: {"id":"err_20240807190605x473FD8E5","code":400,"type":"InvalidPayload","message":"request/body/rows must NOT have more than 1000 items"} (use muteHttpExceptions option to examine full response)

Hmm. You might have to split it up then, and make multiple API calls. Or maybe @rich-battery-69172 knows a fix

Thanks, I split up my data at 1000 rows and commented out the line to delete all the rows before adding the new data. It seems to work now.

@User The only error i'm getting is one regarding the row cannot exceed 4kb? Is there a way round this??

Hey there, You can read about it here https://botpress.com/docs/limits-and-quotas-of-botpress-cloud#quotas:~:text=The%20maximum%20number%20of%20row%20sizes%20in%20a%20table.%20However%2C%20you%20can%20increase%20it%20from%20the%20factors%20section%20from%20the%20API%20(Read%20More%20Here)