0

I am currently running a google script to create a PDF from Google Form Inputs. I have moved my Form Response Sheet data to the 3rd row(image attached) (using the 2nd row for another formula). Every time I run the script it's creating a PDF for 1st data entry only. Please suggest the changes I can make so that it gives the output every time i submit the form (trigger has already been set). This is the script-

function myFunction() {
  

}

function createPDF(){
    
    const info = {
      'Timestamp' : ['4/7/2021 16:30:41'],
      'Location Code' : ['3456'],
      'Upload Image' : ['https://drive.google.com/open?id=1mietxRRzJLiOzwU71dJSmHJp9H833nul'],
    }
 
  const pdfFolder = DriveApp.getFolderById("1I7ChP1xRbl7GwnvAcXV_9JIHegvsU2PO");
  const tempFolder = DriveApp.getFolderById("1srcXlFgmh01e4Psw_dxM4bNgarY6tfli");
  const templateDoc = DriveApp.getFileById("1xype5jUa6H8VJ4JSaV-KfccSjK5w6e-hmoI1k6MXSOc");

  const newTempFile = templateDoc.makeCopy(tempFolder);
  const openDoc =  DocumentApp.openById(newTempFile.getId());
  const body = openDoc.getBody();
  body.replaceText("{Location Code}",info['Location Code'][0]);
  body.replaceText(" {Timestamp}",info['Timestamp'][0]);
  body.replaceText( " {Upload Image}",info['Upload Image'][0]);
  
  openDoc.saveAndClose();

  const blobPDF = newTempFile.getAs(MimeType.PDF);
  const pdfFile = pdfFolder.createFile(blobPDF).setName(info['Location Code'][0]+"-"+new Date());
  tempFolder.removeFile(newTempFile);

}

Only highlighted cell is running on each form submit

This is the google doc I'm using for pdf creation

1 Answer 1

0

The script is not reading data from the spreadsheet, it's using the data from the following code lines:

 const info = {
      'Timestamp' : ['4/7/2021 16:30:41'],
      'Location Code' : ['3456'],
      'Upload Image' : ['https://drive.google.com/open?id=1mietxRRzJLiOzwU71dJSmHJp9H833nul'],
    }

First you have to read the data from the spreadsheet.

const spreadsheet = SpreadsheetApp.getActivespreadsheet();
const sheet = spreadsheet.getSheetByName('Form responses 1');
const range = sheet.getRange(3,1,1,4);
const values = range.getValues();

Then you have to replace the values of the arrays assigned to info object by the corresponding values of your spreadsheet. I.E.:

info['Timestamp'][0] = values[0][0];
1
  • 1
    Thank you very much. It's working perfectly well now. Apr 8, 2021 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.