Run Google Sheet Script Running for 1st entry only

Posted on

Run Google Sheet Script Running for 1st entry only – Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula or script parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common script or formula parse errors in Google Sheets, like Run Google Sheet Script Running for 1st entry only, and how to fix it.

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

Answer :

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];

Leave a Reply

Your email address will not be published. Required fields are marked *