Inserting variable data into an existing Google Sheet in Google Scripts

Question

I'm building a script that will ultimately take data from a csv file, populate a spreadsheet, use that spreadsheet to autofill a number of documents, and then automatically e-mail those documents to customers. It's also moving the documents from each time the script runs to a new folder with just that day's reports. I'm pretty new to Google Scripts, and this has been my learning project.

The steps I've got to work so far is the creation of documents for the spreadsheet with dummy data.

I ended up creating a second script to send e-mails which sends e-mails, but it is set up to look for all documents in the folder that are google docs, so it send a copy of every document to each customer. I thought that if I pointed the e-mail back to the original spreadsheet to grab just the correct document IDs (instead of the type) , I could only send customers the reports that belonged to them (all in PDF form). The step of creating a new folder and moving the documents into it afterwards works fine.

What I'm having an issue with is inputting data into my document ID column in the original spreadsheet. I have been able to watch it put the document ID of the first document into every row that has info to iterate over in the column, and then replace every row again with the second document's ID, etc.

I looked for ways to add data to a spreadsheet. Every method I've found so far involves creating a new column or row with new information from data within the spreadsheet. I need to put in data that I'm just now creating outside of the spreadsheet and then put it in the right place so I can point to it later.

I've gone over the methods within the documentation. It looks like .getCell.setvalue(variable) should work...if I could find a way to get the cell from the range (Which keeps showing me out of range).

function createDocument() {
var headers = Sheets.Spreadsheets.Values.get('17jXy9IlLt8C41tWEG5iQR31GjzOftlJs73y2L_0ZWNM', 'A1:P1');
var tactics = Sheets.Spreadsheets.Values.get('17jXy9IlLt8C41tWEG5iQR31GjzOftlJs73y2L_0ZWNM', 'A2:P');
var templateId = '1DU13OJHWyYnO5mLFovm97pWwXuU7ZTTDVJb2Mpdeebk';

for(var i = 0; i < tactics.values.length; i++){

var customer = tactics.values[i][0];
var pcname = tactics.values[i][1];
var date = tactics.values[i][2];
var virusvalue = tactics.values[i][3];
var cpuuse = tactics.values[i][4];
var ramuse = tactics.values[i][5];
var harddrive = tactics.values[i][6];
var netuse = tactics.values[i][7];
var downtime = tactics.values[i][8];
var cpuperform = tactics.values[i][9];
var ramperform = tactics.values[i][10];
var harddiskperform = tactics.values[i][11];
var reccomend = tactics.values[i][13];
var custID = tactics.values[i][14];
var newdoc = tactics.values[i][15];

//Make a copy of the template file
var documentID = DriveApp.getFileById(templateId).makeCopy(DriveApp.getFolderById('1zV-WpzUKoRurE9FnBcfjBygBA5rCO67I')).getId();

//rename the copied file
DriveApp.getFileById(documentID).setName('MCHA ' + customer + ' - ' + pcname);

Logger.log('value1 ' + documentID);

//THIS IS THE AREA I'M TRYING TO FIX
var ss = SpreadsheetApp.openById('113aqWVAjjUYCmI2oFc_BTbXkWMPFPjk_SschsKEx6qU');
var cell = ss.getRange('P2:P').getCell([i], [15]);
cell.setValue(documentID);
SpreadsheetApp.flush();

Logger.log('value2 ' + documentID);

 //This area has code to replace the tags in the document with values from the spreadsheet.  I cut it for not being relevant.        
  }
}

Obviously defining the range is just going to fill and autofill each cell. That code worked when I tried it

I originally tried using the variable from my earlier list for the autofill, but I've since realized that shouldn't work at all.

I tried to set the range of cells and then set the current cell by the same notation ([i][15] etc.) which throws an 'Range not found' error. I feel like I must be missing some syntax.

A link to the folder with all the documents is below, if that helps. https://drive.google.com/drive/folders/1sRhti3R8R-Cym0hr2S4XkAVn3wyBbSRu?usp=sharing

ADW ADW · Accepted Answer · 2019-08-15T14:30:09

I may not have entirely understood the problem you are facing. But I can see the cause of the 'Range not found' error.

Your loop starts with a value of 0 for i. This causes the script to look for a range called P0 in the first iteration.

One way to avoid that is to try :

 var cell = ss.getRange("P"+(i+1));

For code efficiency, I'd also suggest moving some of the code outside the loop. For example, the following line runs each time in the loop. But it could be defined once outside the loop and then the variable ss could be reused inside the loop.

var ss = SpreadsheetApp.openById('113aqWVAjjUYCmI2oFc_BTbXkWMPFPjk_SschsKEx6qU');

Similarly, you could define the template file outside the loop and then sue it inside to make copies:

var templateFile = DriveApp.getFileById(templateId); // Outside the loop

And then inside the loop:

var documentID = templateFile.makeCopy(DriveApp.getFolderById('1zV-WpzUKoRurE9FnBcfjBygBA5rCO67I')).getId();

Google apps script best practices suggests minimising calls to the spreadsheet, i.e. get the data in one call, process it, and then post the data to the sheet in one call. More here.

Hope this helps.

Inserting variable data into an existing Google Sheet in Google Scripts

1 Answers