Apps Script: Append row and automatically add additional details that newly created row-CodePudding

I have 2 server-side scripts handling information coming from an html form. The first script stores the user input in the last available row in my Google sheet. The second script adds some additional information to the next available cells of that newly created row in the same Google sheet.

Both scripts have been working pretty fine. But it is a little bit annoying that I have to always run the second script manually or with a time based trigger. So, here is my question: How can I make these 2 scripts run automatically one after another or combine them into one single script?

Script 1 (appends new row)

function AddRecord(comapny, salutation, name, phone, email, appointment, room) {
  var url = 'SHEET_URL';
  var ss = SpreadsheetApp.openByUrl(url);
  var webAppSheet = ss.getSheetByName("SHEE_ID");

  webAppSheet.appendRow([comapny, salutation, name, phone, email, appointment, room]);
}

Script 2 (adds additional details to the newly appended row)

function addMoreDetailsToRecord() {
  var url = 'SHEET_URL';
  var ss = SpreadsheetApp.openByUrl(url);
  var webAppSheet = ss.getSheetByName("SHEE_ID");

  var data = webAppSheet.getDataRange().getValues();
  var data_len = data.length;
  for (var i = 0; i < data_len; i  ) {
    if (data[i][7] == "8:00 AM" && data[i][9] === "" && data[i][10] === "") {
      webAppSheet.getRange(i   1, 9).setValue("NEW APPOINTMENT");
      webAppSheet.getRange(i   1, 10).setValue("ROOM NUMBER ONE");
      webAppSheet.getRange(i   1, 11).setValue("NAME OF ORGANIZER");
    }
    else if (data[i][7] == "10.00AM" && data[i][9] === "" && data[i][10] === "") {
      webAppSheet.getRange(i   1, 9).setValue("NEW APPOINTMENT");
      webAppSheet.getRange(i   1, 10).setValue("ROOM NUMBER TWO");
      webAppSheet.getRange(i   1, 11).setValue("NAME OF ORGANIZER");
    }
  }
}

Thank you so much in advance for your help and hints.

CodePudding user response：

You can call your second script from within your first one.

function AddRecord(comapny, salutation, name, phone, email, appointment, room) {
  var url = 'SHEET_URL';
  var ss = SpreadsheetApp.openByUrl(url);
  var webAppSheet = ss.getSheetByName("SHEE_ID");

  webAppSheet.appendRow([comapny, salutation, name, phone, email, appointment, room]);
  addMoreDetailsToRecord(); //just call your second function. you can add arguments to it if needed, too
}

This way, every time you run AddRecord(), addMoreDetailsToRecord() will also be executed at the end.

CodePudding user response：

As @Rodrigo Biffi says, if you want the script to run right after AddRecord, you can always call the function at the end of it. To make sure that the changes have been executed you could use SpreadSheet.flush()

`Code.gs`

function AddRecord(comapny, salutation, name, phone, email, appointment, room) {
  var url = 'SHEET_URL';
  var ss = SpreadsheetApp.openByUrl(url);
  var webAppSheet = ss.getSheetByName("SHEE_ID");
  webAppSheet.appendRow([comapny, salutation, name, phone, email, appointment, room]);
  SpreadSheet.flush()
  addMoreDetailRow(webAppSheet)
}

function addMoreDetailsToRecord(webAppSheet) {
  var data = webAppSheet.getDataRange().getValues();
  var data_len = data.length;
  for (var i = 0; i < data_len; i  ) {
    if (data[i][7] == "8:00 AM" && data[i][9] === "" && data[i][10] === "") {
      webAppSheet.getRange(i   1, 9).setValue("NEW APPOINTMENT");
      webAppSheet.getRange(i   1, 10).setValue("ROOM NUMBER ONE");
      webAppSheet.getRange(i   1, 11).setValue("NAME OF ORGANIZER");
    }
    else if (data[i][7] == "10.00AM" && data[i][9] === "" && data[i][10] === "") {
      webAppSheet.getRange(i   1, 9).setValue("NEW APPOINTMENT");
      webAppSheet.getRange(i   1, 10).setValue("ROOM NUMBER TWO");
      webAppSheet.getRange(i   1, 11).setValue("NAME OF ORGANIZER");
    }
  }
}

If the scripts are in two separate projects you could always create a Library and add it to your main project.

Documentation

Best Practices using Libraries