Appsheet tasks to appscript - issue with parameters not passed

[Davgin]

Hello,

I am creating an automation in appsheet that is calling appscript to create a new google task when a new task is being created in appsheet.

My challenge is that it seems like the appsheet automation is not passing the task parameters to the script. I have validated the script by hard coding the parameters and it works.

Appsheet and my script have the following parameters:

1. company_name: this field is automatically populated in the appsheet form. the script is checking if there is a google tasks list with this name and if not it will create it.

2. task_title: to create the task title

3. task_description: to add the description to the task

4. planned_date: in appsheet it is called planned date and is mapped to the google task due date.

function createGoogleTask(companyName, taskTitle, taskNotes, dueDate) {
  try {
    // Validate and log the input parameters
    if (!companyName || companyName.trim() === "") {
      throw new Error('Company Name is required but was not provided.');
    }
    taskTitle = taskTitle || "Default Task Title"; // Use default if not provided
    taskNotes = taskNotes || "No notes provided."; // Use default if not provided
    dueDate = dueDate ? new Date(dueDate).toISOString() : null; // Handle null or empty dueDate

    Logger.log('Company Name: ' + companyName);
    Logger.log('Task Title: ' + taskTitle);
    Logger.log('Task Notes: ' + taskNotes);
    Logger.log('Due Date: ' + dueDate);

    // Get all task lists
    var taskLists = Tasks.Tasklists.list().items;
    Logger.log('Retrieved Task Lists: ' + JSON.stringify(taskLists));

    // Find the task list with the name matching the company name
    var taskListId = null;
    if (taskLists && taskLists.length > 0) {
      for (var i = 0; i < taskLists.length; i++) {
        Logger.log('Checking Task List: ' + taskLists[i].title);
        if (taskLists[i].title === companyName) {
          taskListId = taskLists[i].id;
          Logger.log('Found Task List ID: ' + taskListId + ' for Company Name: ' + companyName);
          break;
        }
      }
    }

    // If no matching task list is found, create one
    if (!taskListId) {
      Logger.log('No matching task list found. Creating a new task list for Company Name: ' + companyName);
      var newTaskList = {
        title: companyName
      };
      var createdTaskList = Tasks.Tasklists.insert(newTaskList);
      taskListId = createdTaskList.id;
      Logger.log('Created new Task List ID: ' + taskListId + ' for Company Name: ' + companyName);
    }

    // Validate taskListId before proceeding
    if (!taskListId || taskListId.trim() === "") {
      throw new Error('Invalid Task List ID. taskListId is empty or null.');
    }

    // Create the task object
    var task = {
      title: taskTitle,
      notes: taskNotes,
      due: dueDate
    };

    // Insert the task into the found or created task list
    Logger.log('Attempting to create task in Task List ID: ' + taskListId);
    var createdTask = Tasks.Tasks.insert(task, taskListId);

    // Log the created task
    Logger.log('Task created in list: ' + companyName + ', Task title: ' + createdTask.title);

    // Return success message
    return 'Task created successfully in list "' + companyName + '": ' + createdTask.title;
  } catch (e) {
    Logger.log('Failed to create task: ' + e.message);
    return 'Failed to create task: ' + e.message;
  }
}

you can see that I am also calling google tasks in the beginning to list all the existing tasks lists in tasks and I am getting results that matches the company_name in appsheet.

here is my automation:

Do you have any idea why the function parameters are not being passed to the script?

[WillowMobileSys]

What evidence doe have that 1) the columns are properly populated 2) that the script is receiving no values?

Here's what I would check:

1)  Check the AppSheet columns and make sure there are no restrictions that might be interfering with the columns passed.

2)  I assume you can see that the automation is actually getting triggered??  Check AppSheet's Monitor Log.  There will be a an entry for each attempt to run the automation.  Check the entry for any errors AND to confirm the values the parameter columns have at the time the automation is called.

3)  I see that you are writing to the script log.  I would move your parameter log statements to the very top of the try block to ensure the logs are written as soon as the method is executed - at least until the issue is resolved.  When the script is run, there should be a log entry for each run attempt.  Are you getting those?  If so, what is being logged for each parameter?  Blanks?

4)  How did you "hard code" the parameters?  If you simply assigning them values at the top of the script method, then you are not exercising the passing of values.  Instead create a test method that calls your existing method and passes test values that way.

I hope this helps!

[Davgin]

Some more screenshots:

Maybe the problem is here? in step 3 of the automation? (next screenshot)

the hard-coded script, changing the parameters at the top:

function createGoogleTask(companyName, taskTitle, taskNotes, dueDate) {
  try {
    // Use default values if any parameters are undefined
    companyName = companyName || "Default Company Name";
    taskTitle = taskTitle || "Default Task Title";
    taskNotes = taskNotes || "No notes provided.";
    dueDate = dueDate || null;

    // Log the input parameters
    Logger.log('Company Name: ' + companyName);
    Logger.log('Task Title: ' + taskTitle);
    Logger.log('Task Notes: ' + taskNotes);
    Logger.log('Due Date: ' + dueDate);

    // Get all task lists
    var taskLists = Tasks.Tasklists.list().items;
    Logger.log('Retrieved Task Lists: ' + JSON.stringify(taskLists));

    // Find the task list with the name matching the company name
    var taskListId = null;
    if (taskLists && taskLists.length > 0) {
      for (var i = 0; i < taskLists.length; i++) {
        Logger.log('Checking Task List: ' + taskLists[i].title);
        if (taskLists[i].title === companyName) {
          taskListId = taskLists[i].id;
          Logger.log('Found Task List ID: ' + taskListId + ' for Company Name: ' + companyName);
          break;
        }
      }
    }

    // If no matching task list is found, create one
    if (!taskListId) {
      Logger.log('No matching task list found. Creating a new task list for Company Name: ' + companyName);
      var newTaskList = {
        title: companyName
      };
      var createdTaskList = Tasks.Tasklists.insert(newTaskList);
      taskListId = createdTaskList.id;
      Logger.log('Created new Task List ID: ' + taskListId + ' for Company Name: ' + companyName);
    }

    // Validate taskListId before proceeding
    if (!taskListId || taskListId.trim() === "") {
      throw new Error('Invalid Task List ID. taskListId is empty or null.');
    }

    // Create the task object
    var task = {
      title: taskTitle,
      notes: taskNotes,
      due: dueDate ? new Date(dueDate).toISOString() : null
    };

    // Insert the task into the found or created task list
    Logger.log('Attempting to create task in Task List ID: ' + taskListId);
    var createdTask = Tasks.Tasks.insert(task, taskListId);

    // Log the created task
    Logger.log('Task created in list: ' + companyName + ', Task title: ' + createdTask.title);

    // Return success message
    return 'Task created successfully in list "' + companyName + '": ' + createdTask.title;
  } catch (e) {
    Logger.log('Failed to create task: ' + e.message);
    return 'Failed to create task: ' + e.message;
  }
}

[Davgin · Accepted solution]

I dont what happened, since I did not changed anything since posting this, but now it is working...