google-apps-scriptgoogle-sheetssubmitsubmit-button

Google Sheets: submit button to pass values from Workbook A to Workbook B


I am new to this and I could use some help.

I am creating a very simple form in which a person will enter data in 4 cells, then press a Submit button for this info to be recorded in a different sheet.

I pretty much got the exact thing I want when it comes to submitting the data to a different Sheet within the same Workbook, however I would need this data to be recorded in a different Workbook altogether.

I have been looking for a solution for some time, but the most similar thing I found are not quite what I need.

The code I have at the moment makes sure all cells are filled, interrupting the code otherwise, copies the info from "Sheet1" to "Sheet2", and clears the cells once it's done. A following submission would continue adding data in the next row.

I would need the exact same thing, but that information on "Sheet1" in "Workbook A" to be sent to "Sheet2" in "Workbook B"

Here is the code I have at the moment:

//Clear form
function ClearF(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formS = ss.getSheetByName("Form");

  var rangesToClear = ["E8", "H8", "K8", "N8"];
   for (var i=0; i<rangesToClear.length; i++) {
     formS.getRange(rangesToClear[i]).clearContent();
   }
}
//----------------------------------------------------
//Input values for Agent Form
function SubmitHours() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formS = ss.getSheetByName("Form");
  var dataS = ss.getSheetByName("Database");

  var values = [[formS.getRange("E8").getValue(),
                 formS.getRange("N8").getValue(),
                 formS.getRange("H8").getValue(),
                 formS.getRange("K8").getValue()]];

if (values[0].some(val => val === '')) {throw new Error ("REQUIRED FIELD IS EMPTY");
return;
}
dataS.getRange(dataS.getLastRow()+1, 1, 1, 4).setValues(values);
Browser.msgBox("We got it. Thanks!");

ClearF();
}

Any Help would be greatly appreciated.


Solution

  • Submit and Clear

    function SubmitHours() {
      const ss = SpreadsheetApp.getActive();
      const fsh = ss.getSheetByName("Sheet0");
      const dss = SpreadsheetApp.openById(gobj.globals.ssid);
      const dsh = dss.getSheetByName("Sheet1");
      const rgl = fsh.getRangeList(["E8","N8","H8","K8"]);
      let vs = rgl.getRanges().map(r => !r.isBlank()?r.getValue():null).filter(e => e);
      if (vs.length == rgl.getRanges().length) {
        dsh.getRange(dsh.getLastRow() + 1, 1, 1, 4).setValues([vs]);
        rgl.getRanges().forEach(r => r.clearContent());
      } else {
        ss.toast("Incomplete Data");
      }
    }