Trigger Google script without Form submission

Question

I have a Google script that runs on a trigger on form submit.

When the form is submitted a new row is added to a linked spreadsheet, and the script runs some actions to create a new folder and Google doc.

I'd like to amend this by adding new rows via Zapier and not the form sumbission.

Adding the new Rows with Zapier is easy,

However, in setting the script trigger to the 'On change' event from 'On form submit', I get the error;

TypeError: Cannot read property '0' of undefined

I think in using the e.values that the script is expecting these from the form submission, but is failing because they are absent in Zapier added rows.

Problem: I don't know how to resolve the error and allow the script to run in the same way when a form is submitted as when a new row is added from Zapier? https://i.sstatic.net/vJuRw.png

---

What I have in place;

    function autoFillGoogleDocFromForm(e) {
    var Timestamp = e.values[0];
    var NAME = e.values[1];
  
    var file = DriveApp.getFileById('1234');
    var folder = createNewFolder(); 
    var copy = file.makeCopy(NAME, folder); 
    var newId = copy.getId();
  
    try{
    var doc = DocumentApp.openById(newId).addEditor(email);
    }
    catch(error){
    var doc = DocumentApp.openById(newId);
    }
  
    var body = doc.getBody();  
    body.replaceText('{{Name}}', Name);
    doc.saveAndClose();                                             
                                       
    }

    <!--This creates a Google drive folder -->

    function createNewFolder() {
    var ss = SpreadsheetApp.getActive();
    var names = ss.getSheetByName("Sheet1");
    var POSNAME = names.getRange(names.getLastRow(),
    2).getValue(); 
    var folders = DriveApp.getFoldersByName(NAME);
    while (folders.hasNext()) { 
    folder = folders.next();    
    if(folder.getName()==NAME){
     return folder;
        }
    }
    var parentFolder=DriveApp.getFolderById("1234");
    return parentFolder.createFolder(NAME);
    }

Answer 1

I think you're missing a level within your value array and you need conver your e into a range before grabbing the values. Remember spreadsheets always have two-dimensional arrays.

I set the below code up in blank spreadsheet to run on edit and it's bringing the values into cells a1 and b1. It's got excessive variables just to illustrate what's happening. You could obviously reduce these but ultimately I think this gets you the values you're looking for your variables Timestamp and NAME.

/**
 * @OnlyCurrentDoc
 */
const mySS = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
function onEdit(e){
  var theRange = e.range;
    var allValues = theRange.getValues();
    var singleRowValues = allValues[0];
    var firstColumnofRowValue = singleRowValues[0];
    var secondColumnofRowValue = singleRowValues[1];
    var Timestamp = firstColumnofRowValue;
    var NAME = secondColumnofRowValue;
    //Test Output
    mySS.getRange("A1").offset(0,0,1,2).setValues([[Timestamp,NAME]]);
}