I am using Google Apps Script (which uses JavaScript) on a Google Sheet doc to carry out a data collection, cleanse and extraction process (to CSV). The whole script runs well apart from one of the functions (see code below).
For some reason it doesn’t seem to apply itself to the very last worksheet in the Google Sheet or the last item stored in the sheets array.
Once it gets to the last item in the sheets array, it will insert the two new columns (client_id and client_name) but not actually set the respective values for these two columns. For example, if client_id = 6 and client_name = "Amazon", these two values will be inserted per row of data in the respective columns for that worksheet.
It will then continue the rest of the code, creating the CSV files and then removing the newly added columns as it should.
Work-around (not ideal) #1: if I insert a blank worksheet and place it at the end, it will actually apply itself to all the useful worksheets before it – which does resolve the issue – however its not an efficient solution.
Work-around #2: I was thinking to rewrite the code as below:
from this (original): for (let i = 0; i < sheets.length; i++) { ... }
to this (new): for (let i = 0; i <= sheets.length; i++) { ... } with a nested if statement which says something along the lines of “if i = sheets.length, do nothing — else, do the usual”
I still need to test this one but yet again feels inefficient.
Current Code:
/* Adds in two columns, one for client id and one for client name to each row.
Columns will be inserted just before the data extraction process and then removed after. */
function insertClientIDsAndNames(client_id, client_name, spreadsheet_id) {
// all the worksheets this function will apply itself to
var spreadsheet = SpreadsheetApp.openById(spreadsheet_id);
var sheets = [];
// collects all the sheets
for (let i = 0; i < spreadsheet.getSheets().length; i++) {
sheets.push(spreadsheet.getSheets()[i].getSheetName());
}
for (let i = 0; i < sheets.length; i++) {
var sheet = spreadsheet.getSheetByName(sheets[i]);
// inserts two new columns at the start of the sheet
sheet.insertColumns(1, 2);
// insert column header names
sheet.getRange(1, 1).setValue("client_id");
sheet.getRange(1, 2).setValue("client_name");
// returns the position of the last row/column that has content
var lastRow = sheet.getLastRow();
var range = sheet.getRange(2, 1, lastRow, 2);
var values = range.getValues();
// formats the range to plain text
range.setNumberFormat('@STRING@');
// sets the client id
for (let i = 1; i < values.length; i++) {
sheet.getRange(i + 1, 1).setValue(client_id);
}
// sets the client name
for (let i = 1; i < values.length; i++) {
sheet.getRange(i + 1, 2).setValue(client_name);
}
}
// creates the csv files
dataExtractAll(spreadsheet_id);
// deletes the two new columns that were just created
for (let i = 0; i < sheets.length; i++) {
var sheet = spreadsheet.getSheetByName(sheets[i]);
sheet.deleteColumns(1, 2);
}
};
Any help would be great as I feel it is a simple solution and I can’t quite see it for some reason!
Cheers.
