How To Get Spreadsheet Values which was recently summited from Google form Using Google Apps Script-CodePudding

I have a Google form that submits values to the Google spreadsheet. At the same time I print details to a PDF which was save in my google drive folder. operations are working find.

Meanwhile I updated my spreadsheet with another field call "reference ID" and I need to get reference ID value to the App scripts. I can get the 1 column 2 row value from the spreadsheet on following code and works fine.

const sh1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1").getRange(1,2).getValue();

But, I need to get the value in a 2 column and row that I updated from the form submitting. It cannot be the last row I guess, since form may be updated with multiple submit ions at the same time.

Here is my full Apps Script

//Form submission
function afterFormSubmit(e) {
  const info = e.namedValues;
  const referId = getSheelValue();
  createPDF(info, referId);
}
//Getting values from spreadsheet
function getSheelValue(){
  //here I need to get the value in a 2 column and row submitted from the form
  const sh1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1").getRange(1,2).getValue(); 
  return sh1;
}
//Save PDF
function createPDF(info, referId) {
  const pdfFolder = DriveApp.getFolderById("1xHzPbN17uF5fet7C");
  const tempFolder = DriveApp.getFolderById("1nV6tOC99hj5G8Bbvg0O9Lxr");
  const templateDoc = DrveApp.getFileById("1mv_5R5lkzOBuGCmwEe7BYd4S2IQkwE");
  const newTempFile = templateDoc.makeCopy(tempFolder);

  const openDoc = DocumentApp.openById(newTempFile.getId());
  const body = openDoc.getBody();

  body.replaceText("{reff}", referId);

  body.replaceText("{date}", info['Timestamp'][0]);
  body.replaceText("{emal}", info['Email'][0]);
  body.replaceText("{name}", info['Name'][0]);

  openDoc.saveAndClose();

  const blobPDF = newTempFile.getAs(MimeType.PDF);
  const pdfFile = pdfFolder.createFile(blobPDF).setName(info['Name'][0]   info['Timestamp'][0]);

  newTempFile.setTrashed(true);
}

CodePudding user response：

method 1. add a column into the answer to define if that row of data is processed or not:

script:

function onSub() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const values = sheet.getDataRange().getValues();
  const toUpdate = {};
  for(const [i,row] of values.entries()) {
    const status = row[2];
    if(!status) {
      toUpdate[i] = row;
      toUpdate[i][2] = true;
    }
  }
  for(const [rowIndex, rowValues] of Object.entries(toUpdate)) {
    const rowNum = 1   Number(rowIndex);
    sheet.getRange(rowNum,3).setValue(true);
    console.log(`row ${rowNum} recieved new data: ${JSON.stringify([rowValues])}`);
  }
}

method 2. use an installable trigger with event object:

script:

function onSub(e) {
  console.log(e.namedValues);
}

Installable trigger can be activated on form submit. event object can return the sheet object, range object value object, etc.

to learn more:

Installable trigger> https://developers.google.com/apps-script/guides/triggers/installable

Event object> https://developers.google.com/apps-script/guides/triggers/events

CodePudding user response：

Finally I found a way to get the value by add the following.

const sh1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1").getRange(e.range.rowStart,2).getValue();