Last active
January 20, 2024 09:43
-
-
Save nitobuendia/ba895f9980acc41f7f51dc017702689a to your computer and use it in GitHub Desktop.
Apps Script to import Facebook Messages (JSON format) into a spreadsheet
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Licensed under the Apache License, Version 2.0 (the "License"); | |
// you may not use this file except in compliance with the License. | |
// You may obtain a copy of the License at | |
// | |
// https://www.apache.org/licenses/LICENSE-2.0 | |
// | |
// Unless required by applicable law or agreed to in writing, software | |
// distributed under the License is distributed on an "AS IS" BASIS, | |
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. | |
// See the License for the specific language governing permissions and | |
// limitations under the License. | |
/** @const {string} Id of the Drive folder with the Facebook data. | |
* | |
* You should get it from the URL: | |
* https://drive.google.com/drive/folders/<DRIVE_FOLDER_ID> | |
*/ | |
const DRIVE_FOLDER_ID = ''; | |
/** @const {string} Id of the spreadsheet where to add the data. | |
* | |
* You should get it from the URL: | |
* https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID>/edit#gid=0 | |
*/ | |
const SPREADSHEET_ID = ''; | |
/** @const {number} Number of messages to import. */ | |
const MESSAGES_TO_LOG = 1; // Set to 0 to import all messages. | |
/** @{!Set<string>} Sender names to exclude. | |
* | |
* Normally it would be yours if you want to import only other people's | |
* messages. | |
*/ | |
const IGNORE_NAMES = new Set([ | |
'Your Name', | |
]); | |
/** | |
* Iterates a folder to find all files, and import Facebook messages into | |
* a spreadsheet. Folder is defined by DRIVE_FOLDER_ID and the spreadsheet | |
* by SPREADSHEET_ID. | |
* | |
* Functions are put on a closure to avoid the function names from appearing on | |
* the run menu. This makes it easier for users to know which function to run. | |
*/ | |
const _importFacebookMessages = (() => { | |
const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID); | |
if (!spreadsheet) throw new Error(`Spreadsheet ${SPREADSHEET_ID} not found.`); | |
const sheets = spreadsheet.getSheets(); | |
const sheet = sheets[0]; | |
if (!sheet) { | |
throw new Error(`No sheets found within spreadsheet ${SPREADSHEET_ID}.`); | |
} | |
/** | |
* Adds messages into the sheet. | |
* @param {!Array<string>} message to append. Each element is a cell. | |
*/ | |
const appendMessage = (message) => { | |
console.log('Adding message row', message); | |
sheet.appendRow(message); | |
} | |
/** | |
* Processes message data following rules and imports into the sheet. | |
* @param {!Array<!Object> messages in Facebook JSON format to import. | |
*/ | |
const importMessages = (messages) => { | |
let mCounter = 0; | |
for (let i = messages.length - 1; i >= 0; i--) { | |
const message = messages[i]; | |
const senderName = message?.sender_name; | |
const timestamp = message?.timestamp_ms; | |
const content = message?.content; | |
if (!senderName || !timestamp || !content) { | |
console.log(`Skipping message. Not all data found.`, message); | |
continue; | |
} | |
if (IGNORE_NAMES.has(senderName)) { | |
console.log(`Skipping message. Participant is ${senderName}.`, message); | |
continue; | |
} | |
// Date in YYYY-MM-DD. | |
const messageDate = new Date(timestamp); | |
formattedMessageDate = messageDate.toISOString().substring(0, 10); | |
mCounter++; | |
appendMessage([senderName, formattedMessageDate, content]); | |
console.log(`Found message ${mCounter} to add to spreadsheet!`, message); | |
if (MESSAGES_TO_LOG > 0 && mCounter >= MESSAGES_TO_LOG) { | |
console.log(`Logged ${mCounter} messages. Moving to next file.`) | |
return; | |
} | |
} | |
if (mCounter === 0) console.log('No messages found on this file.'); | |
}; | |
/** | |
* Processes an individual (JSON) file and import its messages. | |
* @param {!File} file in Google Drive (Apps Script) from which to import | |
* messages. | |
*/ | |
const importFile = (file) => { | |
const filename = file.getName(); | |
console.log(`Processing file: ${filename}`); | |
let fileContent; | |
let jsonData; | |
try { | |
fileContent = file.getBlob().getDataAsString(); | |
jsonData = JSON.parse(fileContent); | |
} catch (e) { | |
console.error(e); | |
console.log(`Skipping file. Contents do not look like JSON.`); | |
return; | |
} | |
const messages = jsonData?.messages; | |
if (!messages) { | |
console.log(`Skipping file. No messages found on this file.`); | |
return; | |
} | |
importMessages(messages); | |
}; | |
/** | |
* Finds subfolders within a folder and processes them. | |
* @param {!Folder} parentFolder Folder in which to look for subfolders. | |
*/ | |
const iterateSubFolders = (parentFolder) => { | |
const subFolders = parentFolder.getFolders(); | |
while(subFolders.hasNext()) { | |
const subFolder = subFolders.next(); | |
processFolder(subFolder); | |
} | |
}; | |
/** | |
* Finds files within a folder and imports its messages into a spreadsheet. | |
* @param {!Folder} parentFolder Folder in which to look for files to import. | |
*/ | |
const iterateFiles = (parentFolder) => { | |
const files = parentFolder.getFiles(); | |
while(files.hasNext()) { | |
const file = files.next(); | |
importFile(file); | |
} | |
}; | |
/** | |
* Processes a folder to iterate subfolders and import messages from files. | |
* @param {!Folder} folder Folder in Google Drive to process. | |
*/ | |
const processFolder = (folder) => { | |
const folderName = folder.getName(); | |
console.log(`Processing folder: ${folderName}`); | |
iterateSubFolders(folder); | |
iterateFiles(folder); | |
}; | |
/** | |
* Iterates a folder to find all files, and import Facebook messages into | |
* a spreadsheet. Folder is defined by DRIVE_FOLDER_ID and the spreadsheet | |
* by SPREADSHEET_ID. | |
*/ | |
const importFacebookMessages = () => { | |
const mainFolder = DriveApp.getFolderById(DRIVE_FOLDER_ID); | |
if (!mainFolder) throw new Error('Folder id is incorrect.'); | |
processFolder(mainFolder); | |
} | |
return importFacebookMessages; | |
})(); | |
/** | |
* Iterates a folder to find all files, and import Facebook messages into | |
* a spreadsheet. Folder is defined by DRIVE_FOLDER_ID and the spreadsheet | |
* by SPREADSHEET_ID. | |
* | |
* Run this function on Apps Script to start importing the data. | |
*/ | |
function importFacebookMessages() { | |
_importFacebookMessages(); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment