-1

So I have VERY basic coding skills and I have been trying to piece together code in order to solve this, with no luck. I've started a Javascript Codecademy course but it's not getting to what I believe I need to write this level of code in the time this will be needed.

What I am trying to achieve:

  1. I have a Google Sheet with an 'employee data' tab with ~320 unique employee ID's in a column and a Total Rewards Statement on another tab that is dynamically updated when the employee ID is entered into merged cell C9:D9

  2. I am trying to have the code copy an employee ID from the 'Employee Data' tab and paste it in cell driving the 'Total Rewards Statement' tab. Then generate a PDF

  3. This process would repeat for each employee ID in a loop until the last employee

Employee data tab, containing employee IDs in column B which drive the Total rewards template tab:

Total rewards statement driven by cell C9:D9 (highlighted yellow). Objective is to sequentially change the employee ID then generate a PDF in a loop for all employee IDs on the employee data tab.

Any input is much appreciated!! I've tried googling every piece of this and found promising code, its just a matter of putting it all together that is out of my wheelhouse currently.

TroyG
  • 11
  • 2
  • 1
    Could you share an example of the spreadsheet with some dummy data? Or to show a couple of screenshots at the very least? – Yuri Khristich Apr 04 '22 at 22:09
  • Hey check if this helps: https://stackoverflow.com/questions/70678512/how-to-convert-individual-tabs-in-google-sheets-into-pdf Else you might need to provide a sample and a desired output for the community to try and recreate. But there is definitely a solution to your problem :) – Nami888 Apr 05 '22 at 03:11

1 Answers1

1

This isn't exactly what you're asking for, but I thought I'd share how it can be simple to convert a chart into an image. (You'd probably want to make it prettier)

This saves a PNG for every employee id in the column, ensuring that the chart updates each time C9:D9 is modified by the script.

function MakePNG() {
  const ss = SpreadsheetApp.getActive();
  const sheet1 = ss.getSheetByName('Sheet1');
  const sheet2 = ss.getSheetByName('Sheet2');
  const id_box = sheet1.getRange('C9:D9');
  let ids = sheet1.getRange('A2:A').getValues().filter(x => x != '');
  for (let id of ids) {
    id_box.setValue(id);
    SpreadsheetApp.flush();
    let blob = sheet2.getCharts()[0].getBlob().getAs('image/png');
    DriveApp.createFile(blob).setName('employee_' + id + '_reward.png')
  }
}

test sheet: https://docs.google.com/spreadsheets/d/1p7_-GUf9ti-dcv8PTMYfqlDvpMkJHXdkEnV82LjnU8Q/edit?usp=sharing

example output: https://drive.google.com/file/d/18DmCZXDhgEARZLBcr3Y_dPN2zj0GmDx_/view?usp=sharing

BeRT2me
  • 12,699
  • 2
  • 13
  • 31
  • Thank you this is very helpful. Do you know how I would do this for a PDF of the entire template tab, not just one chart in the tab? – TroyG Apr 05 '22 at 14:58
  • Looks like [this answer](https://stackoverflow.com/a/64961321/11865956) may have what you're looking for~ – BeRT2me Apr 05 '22 at 16:33