I have a Google Sheet spreadsheet containing personal data I collect from people who subscribe to my association. They have to complete an online form and sign it. The data is then sent to the spreadsheet and the signature is imported as a PNG in-cell-image.

I need to extract all the PNG signatures and assign them the specific ID found in the same row so I can later match the signature with the correct personal data when generating a PDF form with another script.

I am currently using the following code I found online. It saves all the images to a folder as PNG files but it assigns names like "image-1", "image-2" in a random order.

Here is the code:

function myFunction() {
  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const url = "https://docs.google.com/spreadsheets/export?exportFormat=xlsx&id="   spreadsheetId;
  const blob = UrlFetchApp.fetch(url, {headers: {authorization: `Bearer ${ScriptApp.getOAuthToken()}`}}).getBlob().setContentType(MimeType.ZIP);
  const xlsx = Utilities.unzip(blob);
  xlsx.forEach(b => {
    const name = b.getName().match(/xl\/media\/(. )/);
    if (name) DriveApp.getFolderById("1mdJbbG_0aF8wjEIuVPsMr9jV31wPINRk").createFile(b.setName(name[1]));
  });
}

How can I edit the code to name each file with the corresponding ID?

Thanks a lot!

EDIT:

I collect data from an online form which is displayed in the image below.

Online Form

When clicking on the signature field, a signature pad opens and allows the user to sign.

Signature Pad

Collected data are then sent to the following spreadsheet stored in Google Drive.

Spreadsheet

The script which sends data from the form to the spreadsheet should be the following

function submit(data) {
  data = JSON.parse(data)
  const headers = SETTINGS.HEADERS.map(({value}) => value)
  const id = Utilities.getUuid()
  const signatures = []
  const values = SETTINGS.HEADERS.map(({key}, index) => {
    if (key === "id") return id
    if (key === "timestamp") return new Date()
    if (!key in data) return null
    if (Array.isArray(data[key])) return data[key].join(",")
    if (data[key].startsWith("data:image")) {
      signatures.push(index)
      return SpreadsheetApp.newCellImage().setSourceUrl(data[key]).build().toBuilder()
    }
    return data[key]

  })
  const ws = SpreadsheetApp.getActive().getSheetByName(SETTINGS.SHEET_NAME.RESPONSES) || SpreadsheetApp.getActive().insertSheet(SETTINGS.SHEET_NAME.RESPONSES)
  ws.getRange(1,1, 1, headers.length).setValues([headers])
  const lastRow = ws.getLastRow()
  ws.getRange(lastRow   1, 1, 1, values.length).setValues([values])
  signatures.forEach(index => {
    ws.getRange(lastRow   1, index   1).setValue(values[index])
  })
  return JSON.stringify({success: true, message: `Grazie per la tua richiesta di iscrizione! ID: ${id}`})

}

The need is to rename the signature image with the submission ID.

In that way, in theory, when I run Tanaike's script to extract the images from the spreadsheet, they should be named with the ID of the corresponding form submission.

As of now, when I run Tanaike's script I get the following output.

Tanaike's script output

Thanks a lot!

EDIT 2:

Thanks to Tanaike's help I was able to name each signature image with the corresponding ID.

Now I need to generate a PDF with the subscriber's data including the signature.

The following is the code I use to generate a PDF from a Google Docs file filled with data pulled from Google Sheets spreadsheet.

function createBulkPDFs() {

const docFile = DriveApp.getFileById("1ZxvyViklifScfOt90YX4R-NgOTbO044rUSeyQfVCzds");
const tempFolder = DriveApp.getFolderById("1yFLVyTtb6tW-UGdf1hkKHdzGIKG7qg2W");
const pdfFolder = DriveApp.getFolderById("1YGG1Y_u2jOMbfSIh3Ajb4kKGU-tg2oWK");
const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses");

const data = currentSheet.getRange(2, 1,currentSheet.getLastRow()-1,15).getDisplayValues();

let errors = [];
data.forEach(row => {
  try{
  createPDF (row[2],row[3],row[7],row[8],row[4],row[9],row[12],row[10],row[11],row[5],row[6],row[13],row[1],row[0],row[2],docFile,tempFolder,pdfFolder);
  errors.push(["Completata"]);
  } catch(err){
    errors.push(["Fallita"]);
  }
});

currentSheet.getRange(2, 16,currentSheet.getLastRow()-1,1).setValues(errors);

}

function createPDF (name,surname,placebirth,daybirth,gender,street,zipcode,city,province,email,phone,instagram,id,timestamp,pdfName,docFile,tempFolder,pdfFolder) {
 
// ID Template Modulo di Iscrizione: 1ZxvyViklifScfOt90YX4R-NgOTbO044rUSeyQfVCzds
// ID tempFolder: 1yFLVyTtb6tW-UGdf1hkKHdzGIKG7qg2W
// ID pdfFolder: 1YGG1Y_u2jOMbfSIh3Ajb4kKGU-tg2oWK

const tempFile = docFile.makeCopy(tempFolder);
const tempDocFile = DocumentApp.openById(tempFile.getId());
const body = tempDocFile.getBody();
body.replaceText("{name}", name);
body.replaceText("{surname}", surname);
body.replaceText("{placebirth}", placebirth);
body.replaceText("{daybirth}", daybirth);
body.replaceText("{gender}", gender);
body.replaceText("{street}", street);
body.replaceText("{zipcode}", zipcode);
body.replaceText("{city}", city);
body.replaceText("{province}", province);
body.replaceText("{email}", email);
body.replaceText("{phone}", phone);
body.replaceText("{instagram}", instagram);
body.replaceText("{timestamp}", timestamp);
body.replaceText("{id}", id);
tempDocFile.saveAndClose();
const pdfContentBlob = tempFile.getAs(MimeType.PDF);
pdfFolder.createFile(pdfContentBlob).setName(pdfName);
tempFile.setTrashed(true);




}

Now I need to retrieve the signature image file as well and put it in the Google Docs file.

The idea is that the script should search for an image named with the specific ID and put it on the Google Docs file replacing the text placeholder {signature}.

Is there a way to achieve that?

CodePudding user response：

I believe your goal is as follows.

• You want to export the images of column "O" in Google Spreadsheet. In this case, you want to use the values of column "B" as the filename.
• The image is put into the cells as CellImage.

Modification points:

• Using XLSX data converted from Spreadsheet, when the image files are directly retrieved from XLSX data, unfortunately, the images cannot correspond to each cell coordinate. I thought that this is the reason of your issue. In this case, it is required to parse the XLSX data. But, I thought that in this case, the script might be a bit complicated. So, in order to retrieve the image data from the XLSX with the cell coordinate, I have created a Google Apps Script library. Ref

In this answer, I would like to propose a sample script using the library.

Usage:

1. Install Google Apps Script library.

Please install DocsServiceApp Google Apps Script library. You can see how to install it at here.

2. Sample script.

Please copy and paste the following script to the script editor of Spreadsheet. And, please set the variables of folderId and sheetName.

function myFunction() {
  const folderId = "###"; // Please set folder ID you want to put the created files.
  const sheetName = "Sheet1"; // Please set your sheet name.

  // Retrieve image data.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const res = DocsServiceApp.openBySpreadsheetId(ss.getId()).getSheetByName(sheetName).getImages();

  // Retrieve IDs from from column "B"
  const folder = DriveApp.getFolderById(folderId);
  const sheet = ss.getSheetByName(sheetName);
  const values = sheet.getRange("B1:B"   sheet.getLastRow()).getValues();
  
  // Create files.
  res.forEach(({ range, image }) =>
    folder.createFile(image.blob.setName(`${values[range.row - 1][0]}.png` || image.blob.getName()))
  );
}

• When this script is run, the image data is retrieved and created as the image file using the filename retrieved from column "B".

Note:

• In this sample script, from your provided sample image, it supposes that the image data and the filename are put in the columns "O" and "B", respectively. Please be careful about this.

Reference:

• DocsServiceApp