Running into error Javascript - Trying to use script to send Google Sheet to another
Posted by misterflocka@reddit | learnprogramming | View on Reddit | 1 comments
I am trying to send my script of one Google sheet link into another Google Sheet cell with a link to my file (1), and the date submitted (2), is this possible, and how can I do this with Apps Script in Google Sheets? I am also trying to have the sheet send a copy of the sheet through my email through a submit tab(3), as well as send to a central repository(4). I had it doing 3-4 but then an error was introduced and it does not do that anymore. The current error message is: Error: Error in addTrackerEntry: Tracker sheet "Sheet1" not found in spreadsheet with ID "18jiRHZfpedExNJSXAe-R9c6\_qWTT-pSqQ-ewlJlzj8E"
​
Any help/advice would be appreciated, I am new to scripts. What do I need to add/change?
const TRACKER\_ID = '18jiRHZfpedExNJSXAe-R9c6\_qWTT-pSqQ-ewlJlzj8E';
const TRACKER\_SHEET\_NAME = 'Sheet1';
const DESTINATION\_FOLDER\_ID = '1C7aG1Y9mdZ4VmMeNSiw5lFDw2yXfFcpM';
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('SUBMIT')
.addItem('Send Request', 'submitSheet')
.addToUi();
}
function addTrackerEntry(linkToFile, builderName, mmEmail, numberOfHomes, unitVolumeYearOne, totalUnitVolume, numberYears, blendedDCM) {
try {
const trackerSs = SpreadsheetApp.openById(TRACKER\_ID);
const trackerSheet = trackerSs.getSheetByName(TRACKER\_SHEET\_NAME);
if (!trackerSheet) {
throw new Error(\`Tracker sheet "${TRACKER\_SHEET\_NAME}" not found in spreadsheet with ID "${TRACKER\_ID}"\`);
}
const data = \['', linkToFile, new Date(), '', builderName, mmEmail, '', numberOfHomes, unitVolumeYearOne, totalUnitVolume, numberYears, blendedDCM\];
trackerSheet.appendRow(data);
} catch (error) {
throw new Error(\`Error in addTrackerEntry: ${error.message}\`);
}
}
function sendSubmissionEmail(mmEmail, builderEmail, linkToCopiedSheet) {
const subject = \`Regional Single Family RFP Request: ${builderEmail}\`;
const body = \`
Below is a request for a Regional Single Family RFP from ${mmEmail}
${linkToCopiedSheet}
\`;
let email = builderEmail;
if (mmEmail !== "") {
email += "," + mmEmail;
}
GmailApp.sendEmail(email, subject, body);
}
function submitSheet() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Submission View");
const builder = sheet.getRange("D3").getValue();
const mmEmail = sheet.getRange("G6").getValue();
const builderEmail = sheet.getRange("G7").getValue();
const numberOfHomes = sheet.getRange("F16").getValue();
const folder = DriveApp.getFolderById(DESTINATION\_FOLDER\_ID);
const copiedFile = DriveApp.getFileById(ss.getId()).makeCopy(builder, folder);
const copiedIRFP = copiedFile.getUrl();
sendSubmissionEmail(mmEmail, builderEmail, copiedIRFP);
// Add call to function that fills out / appends to tracker
addTrackerEntry(copiedIRFP, builder, mmEmail, numberOfHomes, 0, 0, 0, 0);
}
1 Comments
AutoModerator@reddit