Bulk Operations for creating individual emails - SettingUp Google Sheets for Bulk Input/Output

[valeriy]

Hi there,

I discovered Scade and I really love the possibilities of the tool.

I would like to build my first workflow for creating individual emails.

Here for I need a bulk input and a bulk output. Ideally it happens in Google Sheets or in our CRM Hubspot.

The initial prompt is simple “Please write an outreach email based on this {URL} of the prospect”

I would like to put in several thousands of URLs from the Google Sheet column A and to put in the answers in column B (or send them out from Hubspot straight away).

I used the recommended Appscript Code snippet from another use case (this thread [Bulk operations]. 3 Level of working with multiple files and entries - #11 by nik ) and readapted it a bit. Could you help to readapt the code snippet and my workflow even more?

Help is really much appreciated.

Valeriy

My Workflow:

Scade

Code Snippet:

function myFunction() {
  function runAPIsForEachCell() {
  const token = 'MY ACTUAL TOKEN'; // Your actual token
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataRange = sheet.getRange('A2:A'); // Assuming you want to start from row 2.
  const data = dataRange.getValues();

  data.forEach((row, index) => {
    const peUrl = row[0]; // The value from column A (pe_url).

    if (peUrl) {
      // Run the POST request
      const postResponse = postScadeFlow(peUrl, token);
      const taskId = postResponse.id; // Extract task_id from POST response
      
      // Check the status of the task periodically
      const getResponse = waitForTaskCompletion(taskId, token);
      
      // Extract the text_review from the response
      const textReview = extractTextReview(getResponse);
      
      // Store the text_review in column B
      sheet.getRange(index + 2, 2).setValue(textReview);
    }
  });
}

function postScadeFlow(peUrl, token) {
  const url = 'https://app.scade.pro/api/v1/scade/flow/67263/execute';
  const payload = {
    "start_node_id": "B3xQ-start",
    "end_node_id": "Q1sW-end",
    "result_node_id": "Q1sW-end",
    "node_settings": {
      "B3xQ-start": {
        "data": {
          "pe_url": peUrl
        }
      }
    }
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': `Basic ${token}`,
      'Content-Type': 'application/json'
    },
    payload: JSON.stringify(payload)
  };

  const response = UrlFetchApp.fetch(url, options);
  return JSON.parse(response.getContentText()); // Returning the POST response
}

function waitForTaskCompletion(taskId, token) {
  const url = `https://api.scade.pro/api/v1/task/${taskId}`;
  const options = {
    method: 'get',
    headers: {
      'Authorization': `Basic ${token}`,
      'Content-Type': 'application/json'
    }
  };
  
  let isCompleted = false;
  let response;
  
  // Check task status every 5 seconds for up to 2 minutes (adjust as necessary)
  for (let i = 0; i < 100; i++) {  // 24 attempts, 5 seconds each = 2 minutes max
    Utilities.sleep(5000); // Wait for 5 seconds before checking the status again
    response = UrlFetchApp.fetch(url, options);
    const taskStatus = JSON.parse(response.getContentText());
    
    // Check if the task has been completed
    if (taskStatus.status === 'completed' || taskStatus.state === 'done' || taskStatus.result) {
      isCompleted = true;
      break;
    }
  }
  
  return JSON.parse(response.getContentText()); // Return the final GET response when completed
}

function extractTextReview(response) {
  // Navigate the response to find the text_review
  try {
    const textReview = response.result.success.text_review;
    // Clean up any unwanted characters if needed
    return textReview.replace(/\\n/g, ' ').trim();
  } catch (e) {
    return 'No text_review found';
  }
}
}